Getting visuals to sort the way you want them to in Power BI can be trickier than you think. While sorting columns in visuals can be easy, sometimes you need to sort by another column, or define a manual sort order to get things laid out the way that you want them. We’ll explain a few different methods for sorting in Power BI that may not be immediate clear.
We’ll explain the basic methods of sorting visuals in Power BI, and then explain some of the more advanced techniques Power BI developers should be aware of.
Let’s dive in!
Table of Contents
Why Sorting is Important in Power BI
A big part of developing dashboards and reports is presenting information in a way that highlights the most important data first, while making it easy for the end user to modify and adjust reports to explore and visualize the information they find most important to them.
By setting a default sort order in Power BI you can set visuals to show the most important info first, or at times you can have visuals that are sorted in a way that makes absolutely no sense such as when it tries to sort numbers alphabetically or puts months in a seemingly random order.
The Basics of Sorting in Power BI
The first thing to know about sorting visuals in Power BI is that there are different types of sorting options available depending on the type of visual. For example, you can sort a Table visual by multiple columns, but you can’t sort a Matrix visual by multiple columns. The options available for a bar chart, matrix, and column chart also vary, but there are some workarounds if the visuals don’t natively support the desired output.
Sorting Power BI Matrix Visuals
To sort a Matrix Visual in Power BI, you can click on any of the column header titles. There is a small triangle icon at the bottom of each column title that will adjust when sorting lowest to highest or highest to lowest. To adjust the sort column, click on the column header and it will flip the sort order direction.
Other visualization types such as a bar chart or column chart can be sorted by clicking the … button at the top right of the visual when you hover your mouse over it. Click the … button, go to Sort Axis, and you can change which column is defining the sort order and whether you want it to display ascending or descending.
Sorting Power BI Bar and Column Charts
You can also adjust some of the sort settings on a Matrix visual using the … button at the top right. It’s the first place you should look if you don’t immediately see an option to click on a column header to sort by it.
Sorting a Power BI Table Visual by Multiple Columns
While the Matrix visual doesn’t let you sort by multiple columns, you can sort a Power BI table by clicking on one of the column headers. The small triangle icon at the top will change directions to notate which column is being sorted and whether it is ascending or descending.
You can also sort a Table Visual based on multiple columns. To sort on multiple columns in a table, Hold Shift and Left Click on the column headers you want to sort by. Keep Shift held down while making multiple selections.
After the selections are made there will be triangles at the top of each column that is being sorted.
Sorting Power BI Visuals by Hidden Columns
To sort visuals based on values that aren’t displayed within a specific visual, first set up your visual. Then select the column that you want to sort from the Data panel. After selecting a column, the Column Tools tab will activate in the Power BI Ribbon at the top of the screen. Then use the Sort by Column button to sort a column by a field that’s not in the actual visual.
If you need to remove the sort by other column, you can click on a column header or the … button at the top right of a visual. When you set a new sort order it will override the previous one that was set to another column.
Setting Custom Sort Orders in Power BI
To set a custom sort order in Power BI, you have to create a separate table that has your primary column values that you want to have sorted and a second column that has the numeric order that you want the values to be sorted by. The table can either be created by going to Home, Enter Data, which lets you free form type values into a new table, or you could import an Excel or CSV file.
The new table will look something like the one below:
The next step is to Merge the Tables Together. Power BI will not let you sort based on a different table or using a relationship. So you have to get both datasets into a single table. The Merge function in Power Query works like an Excel vlookup in this instance.
To merge tables in Power BI, launch the Power Query Editor. Then click on the Merge Queries button. In the Merge screen, highlight the columns in each dataset that have values in common. Then click OK.
After clicking OK, you can click the left / right arrow icon at the top of the new column to select the ones you want to add from the new dataset.
After merging the columns together, you will have one consolidated dataset with a sort order value.
Use the Sort by Other Column feature to sort by the new manually added sort order index. Using this method you can manually adjust the sort order of any values in a visualization.
Whether you setup the index in Excel or directly in Power BI it’s a fast easy method for setting specific sort orders that otherwise wouldn’t be possible. It’s also possible to create multiple sorting hierarchies using this method. When we’re setting up financial statements, we prefer to set these up in a separate Excel file because it makes it easier to track multiple hierarchies in a single file by adding additional columns.
Fixing Sort Order issues in Power BI
There are a number of things that can cause Power BI visuals to not sort as expected. The most common issue is an incorrect data type assigned to a column. Power BI uses data types to determine how to handle different operations. In terms of incorrect sorting, if a numeric column is defined as text Power BI will not sort it in numeric order.
If you are running into problems sorting columns, check the data type of the column. If it needs to be updated, Column data types can be seen on the data panel, a sigma symbol lets you know that it’s a number, a calendar is a date, and blank is text.
Here’s an example of Power BI trying to sort a column based on a value that’s defined as text instead of a number. As you can see the sort order almost seems random.
Date columns can also have a lot of sorts errors due to missing date tables along with a number of other causes. Check out our comprehensive guide to troubleshooting sorting errors in Power BI to learn more.
Conclusion
Power BI gives developers many different options to sort their visuals. It can be as simple as clicking on a column header to change it from ascending to descending or as complex as importing a separate Excel spreadsheet to set a defined sort order for things like financial statements.
If you don’t immediately see the sort option you are looking for on a specific visual, remember that not all sort options are available on all visuals. You may have to create a separate table to customize the sort order, or at times you may even have to get creative by writing custom DAX formulas for sorting or use a different visual layout altogether.