Sort data in Power BI

This post “Sort data in Power BI” explains how we can sort Power BI visual data by a column which is being used in the visual itself or by some other column which is not being used in the visual itself. We know that Power BI desktop tool provides set of functionalities to create flashy and interactive reports. It supports direct connectivity with many data sources, to know more about Power BI desktop application, visit our previous post “Rise of the Power BI“, to explore drill down functionality in Power BI, visit “Drill down report in Power BI” post.

To demonstrate Power BI visual data sorting, we are going to use below sample query which is extracting internet sales data along with sales territory from AdventureWorksDW2014 database:

SELECT
DST.SalesTerritoryGroup AS GroupName, DST.SalesTerritoryCountry AS CountryName, DST.SalesTerritoryRegion AS RegionName,
ROUND(SUM(FIS.SalesAmount), 2) AS SalesAmount
FROM FactInternetSales FIS
INNER JOIN DimSalesTerritory DST ON FIS.SalesTerritoryKey = DST.SalesTerritoryKey
GROUP BY DST.SalesTerritoryGroup, DST.SalesTerritoryCountry, DST.SalesTerritoryRegion

Below result set returned from the above sample query.

In above result set, we have “GroupName”, “CountryName”, “RegionName” and “SalesAmount” columns to show the sum of total sales amount from FactInternetSales table grouped by sales territory region, territory country and territory group.

Follow below steps to connect and pull the sample data from AdventureWorksDW2014 database using above T-SQL query:

1. Open Power BI desktop application.
2. Click on “Get Data” option and connect to “AdventureWorksDW2014” database using SQL Server database connectivity option. Provide the appropriate information like; server name and database name to connect to SQL server. In our previous post, “Drill down report in Power BI“, we have explained how we can load data from SQL Server into Power BI in step by step way.
3. Use the above T-SQL query in optional SQL statement text box when connecting to the SQL server.
4. Click on “Load” button to import the data into Power BI desktop application. In this demo, we are using the Import option to bring the sample data into Power BI application instead of DirectQuery mode which extracts the data at run-time. We can switch between “DirectQuery” and “Import” data options, to switch, click on Edit button instead of Load button and choose Import or DirectQuery option and then click on OK button.

We have imported the sample data into Power BI application. Next, we are going to create a column chart by placing country on x-axis and sales amount on y-axis. Follow below steps to create a stacked column chart in Power BI:

1. Click on stacked column chart visual.
2. Click on SalesAmount measure column in Fields section. If a numeric (measure) column is selected, typically, it is placed on y-axis (values section of the visual) in Power BI. In case it is not placed in Values section, drag and drop it from Fields section to the Values section of the column chart.
3. Click on CountryName column to add it on x-axis. If a textual filed is selected from Fields section, typically, it is placed on x-axis in Power BI. In case it is not placed in Axis section, drag and drop it from Fields section to the Axis section of the column chart.

We should have a column chart with below configurations at this point:

By default, the data is sorted by the column being used on x-axis.

Sort Power BI data by a column used in the visual

We can sort a chart data by a column which is being used in the visual itself. This method will work with both, Import and DirectQuery modes. Below steps needs to be followed:

• Click on the … (More Options) located at the top right corner in the visual, it will pop up a small menu.
• Click on the “Sort By” option located at the top in the menu.
• List of all the fields being used in the visual will appear in this list, click on the column name by which you want to sort the visual data. In our case, we have CountryName and SalesAmount columns in this list, click on CountryName field to sort the data by this column.

• To switch sort order between ASC or DESC, click on the sort order button located at the top left in the pop up menu which appears after clicking on the “More Options” button.

Sort Power BI data by a column not used in the visual

Sometimes, we need to sort the visual data by a column which is not being used in the visual itself, for example, country names placed on x-axis needs to be ordered by group name column. However, this method will only work with Import data option (Power BI Version: 2.33.4337.281 (March 2016)) and if we are using DirectQuery mode, we will not be able to use this method in Power BI desktop application.

• Click on “Data” button located at the top left in the Power BI application (between “Report” and “Relationships” buttons). It will open the data window with all the queries and field names being used in the report. Below image shows the Data button.

• Below screen will appear after clicking on the Data button. Click on the query name to expand the list of fields and click on the field name in the Fields Section which needs to be sorted.

• Click on the “Modelling” tab (Located right of the Home tab in the main menu).
• Click on the “Sort by Column” button and select the field name from the drop down list by which you want to sort this column values. In this demo, we are choosing GroupName column to sort the CountryName column. By default, same column is use to sort the data in Power BI application.

• To switch between ascending or descending orders, go to the visual and click on A-Z or Z-A button accordingly.

Below is the image of the chart after sorting the country name by group name in ascending order.

Thank you for the reading and please do share, rate and like this post.