Discover how to Fix Power BI Not Sorting Correctly with our in-depth guide. From date sorting to matrix issues, our guide will help you finish developing your Power BI dashboards and reports fast. Sort order issues can be frustrating and have a wide range of causes, luckily most of the fixes are fairly quick and easy.
There are number of reasons that cause Power BI not to sort correctly. The solution will vary for each one.
Let’s dive in!
Table of Contents
Power BI Not Sorting By Column
When troubleshooting Power BI Not Sorting By Column, first go through this simple assessment.
- Are data types assigned correctly?
- Is a date field a fully day, month, year or is it only one of these sections?
- Do I have a date table created and/or continuous dates within my existing dataset?
- Do I need to sort on a specific column or can I sort on a column on a different dataset?
As you think through these options, it will help you identify what the root cause of the problem may be. Often times when we think that a date is not sorting correctly, it’s possible that it’s not a full date but the name of a month which would require a different solution.
Fixing Power BI Sort Errors Caused by Data Types
Whenever you have a sort error check data types in the Power Query Editor First
Power BI will sort Text Columns Alphabetically and Numeric Columns Numerically.
If you have a datatype defined incorrectly in your data model, it will look like the sort order is incorrect. Power BI is doing what it thinks it should by sorting numbers as text. These problems are easy to identify, you will see numbers in the sequence of 1, 10, 2, 3, 4, 5 etc.
Based on an alphabetical sort order 10 comes after 1
Let’s continue looking at some additional causes of sort errors and issues in Power BI
Power BI Not Sorting Dates Correctly
If Power BI is not sorting dates correctly, the first thing you can try is creating a date table and adding the relationship to your existing dataset.
Date Tables serve a unique function within Power BI in that the will assign time intelligence to fields in other tables where it does not exist.
You might find that you go to sort by a date column that looks like a date but it is out of order.
One quick way to add a date table is with the Calendar DAX Formula.
Click on Create New Table and use the formula below to identify a date range.
Note: You can also reference the Max and Min date rather than hard code specific dates to make your Power BI table more dynamic if you expect your relevant reporting date range to update over time.
Guy in a Cube has a great YouTube video on the topic and is generally a great resource to stay on top of new feature releases in Power BI and helpful how to guides.
Power BI Not Sorting by Month
If Power BI is not sorting months chronologically like the picture of the chart below it is usually because the month column being utilized is not being recognized as a date or there is no relationship between the month column and a date hierarchy.
If you look at the data columns available, check if there is a field named “Date Hierarchy”
You can expand the Date Hierarchy and select a portion of the date such as the Month field. As part of a date hierarchy, Power BI maintains the date time intelligence associated with sequential dates.
When you add a month that’s associated with a date, Power BI will know that the sort order should be from January to December.
Fixing a Month sort order when a date column is not available
If your dataset does not contain a date column, the easiest fix is to create one.
You can either use a DAX formula to create a measure or you can add a new column in the Power Query Editor.
Both options to create a New Measure or a New Column are available under the Modeling Tab.
Use a formula like the one below. This specific formula takes the Name of the Month and concatenates the 1st day of the month and the year 2023.
The day and month won’t really matter, all we need is the month sort order.
MonthNumber = MONTH(DATEVALUE(Table[Month] & " 1 2023"))
Once you create a date, you will have the ability to add the month from the date hierarchy like in the example above.
Note: It is possible to create dynamic values to sort on with a New Measure instead of a New Column, but this can sometimes cause issues with circular references.
Power BI Not Sorting Numbers Correctly
When Power BI is not sorting numbers correctly, first check that the data type is correct.
This typically will result in Power BI Sorting 10 before 2. You a sequence such as: 1, 10, 2, 3, 4, 5, etc.
The most common cause of this error is a number assigned a non-numeric data type. If they are setup in Power BI as text or a mix of numbers and text it will cause a sorting error. From a computer’s standpoint ten comes after one and before two in an alphabetical sort order.
Note: You can identify Numeric Data Types in Power BI when there is a Sigma or Sum sign to the left of the name of the column in the data field panel on the right side of Power BI when you’re building a dashboard or report.
Another way to view and edit data types is to enter into the Power Query Editor.
Launching the Power Query Editor
You can get to the Power Query Editor by right clicking the table name and selecting Edit Query. You can also select Transform data from the Table Tools section of the Power BI Toolbar.
After opening the Power Query Editor, you will see a table of all of the columns and fields that are in the data model that you are working on for your Power BI Report.
At the top of each column, the column name has a small icon to the left of the name that notates what type of data a column is. Numeric columns will appear as 123 and Text columns will shows as ABC.
To update data types right click on a column header and select Change Type from the contextual Menu and the desired data type that you want.
If this does not fix the issue of Power BI not sorting numbers correctly, here are some additional items to check.
- Check that a visual is sorting on the correct column and that it is appropriately assigned the correct ascending or descending order.
- Check Sort Column by Other Columns Settings on your visual. Sometimes a sort order is assigned by it is not removed making the column look like it is being sorted incorrectly.
- Double Check number values for rounding. If you format numbers to hide decimals Power BI will still sort by the decimals. To correct this, you may need to round the numbers rather than remove the decimals only through Power BI formatting.
- Check for data cleanliness. Sometimes your data is not clean and you don’t even know it! Things like trailing spaces, null values, and blank cells will all cause sort orders to act inappropriately. These can be fixed by right clicking on columns in the Power Query Editor and replacing Null Values, Trimming, or removing white space.
Power BI Matrix Not Sorting Correctly
When a Matrix visual in Power BI is not sorting correctly, there are a number of potential causes.
- Check that the visual is sorted on the correct column. The matrix visual will notate a small triangle underneath the column that it is being sorted by. The triangle will be facing up or will face down depending on whether you want the sort order ascending or descending.
- Check for Data Type Mismatches. A data type mismatch occurs when a numeric column is defined in Power Query as Text or a Text column could be defined as a number. This most often presents as a numeric order such as 1, 10, 2, 3, 4,5 etc. The number 10 comes after the number 1 and before two.
- Check for Roudning Issues. If you are sorting a Matrix Column based on numeric values it is possible to have decimal places turned off for formating purposes but the numbers are not rounded in the Power Query data model. If this is the case you may find that there are numbers which look the same in the Matrix but are different. Try adding some decimal places in the Matrix Column to see if this happens. To fix it you may need to round the numbers in the data model.
- Check Calculated Columns and Measures. When calculations fail, they can result in null values or erros. Power BI will not interpret these correctly and Power BI will not sort these values. Sometimes they will appear as null or blanks.
- A Matrix Column is Not Part of a Date Hierarchy. If your Matrix looks like the visual below, then it is likely because the Matrix visual is being sorted by a Month column. At times you will have a Month column that is not part of a Date Hierarchy. In these cases, Power BI does not apply time intelligence and Power BI will not sort in chronological order. Click here to go to the section about correcting date sorting in Power BI.
How to Sort a Matrix Visual on Multiple Columns
The Power BI Matrix Visual does not let you natively sort the matrix based on two columns like you can in Microsoft Excel. A common workaround is to create a concatenated sort column.
Note: This is a limitation of Power BI. Microsoft publishes a guide of how a chart is sorted in a report and includes instructions of sorting on multiple columns. Click the first sort column, Hold Shift, then click the second column. However, this method does not work with Matrix visuals.
The easiest solution: Use a Table Visual instead of a Matrix Visual. If this works, you can hold shift and the multiple sort orders will appear at the top of the columns. You can click multiple times while holding shift to toggle between ascending and descending sort order.
Continue reading if the Table Visual is not an option:
An important feature to be aware of is that Power BI allows you to Sort a Column Based on Another Column. This method lets you concatenate multiple columns together to artificially create a sort order hierarchy based on multiple columns in your data model.
For Example:
In the screenshot below, we added 3 calculated columns. The first is a Sort1 column that uses a conditional column that checks IF State = “Florida” THEN 4, IF State = “California” Then 1 etc.
Then we separately added an Index Column.
Note: Index Columns start from either zero, one or a custom value. If you first sort a column based on a value, such as sales highest to lowest, then add an Index column it will allow you to have a secondary sort criteria that goes highest to lowest.
The third step of our process was to concatenate values by adding a custom column that concatenates the two values.
You can concatenate in Power BI using an M Power Query formula that consists of the column name in square brackets with an ampersand or & sign in between the two column names.
By using this method you can create a custom column to control the sort order.
Note: Do be aware that there are some limitations with this method when working with larger datasets. When you go above 10 items, the concatenated values will show up as 29, 210 which would put it out of sequence with 29, 30, 31 etc.
Creating an Index Column to Sort from Highest to Lowest
Another workaround to try, is to sort your data in Power BI Power Query. You can sort data by clicking the drop down arrows at the top of each column.
Once you sort the data the way that you want it, you can create an Index Column step.
When you save and close out of the Power Query editor, the Index Column retains the order of the other columns and will give you a numeric value to sort a visual by.
Try using this technique combined with sorting a Power BI visual based on another column.
Sorting a Power BI Column Based by Another Column
- Click on the name of a field in the data panel of Power BI.
- Click on Column Tools at the top of the Power BI Tool Bar
- Click Sort by Column and choose a different column to sort the month by.
In our data model that we are using for an example, we have several columns that we could potentially sort by, such as the Row No. and Date columns.
Note: You can use this method to sort a column in a Matrix or any other visual even if the column is not shown in the specific visual that you are working with.
Microsoft Learn is a great resource to learn more about sorting column based on the values of other columns. In addition to training and learning material, Microsoft also offers a number of Power BI and related certifications that will help you develop even better dashboards.
Using a Custom Sort Order Table in Power BI
A common problem with Power BI is that sort orders change or there is no specific logic that a sort order occurs in. This occurs very often when creating income statements or other financials in Power BI.
Either the sort order changes over time or it’s so custom that you cannot sort in any numeric or alphabetical order.
When this happens, we like to either create a separate table in Power BI or we maintain an Index File that includes financial statement line items or categories and a second column with a sort order.
Import the Excel file into your Power BI report and add a relationship between the Statement Line and the corresponding column from your other data sources.
Once it’s setup, you can use the Sort Order column for the Excel sheet to set a custom order on a financial statement by following the instructions from Sorting a Power BI Column Based by Another Column
Conclusion and Key Takeaways
Sort order errors and unexpected behavior in Power BI can be frustrating and time consuming to diagnose and correct. We hope you have found a few new ways to fix Power BI not sorting the way that you would like it to.
Remember to first check data types for mismatches, then check that visuals are sorted on the right columns, and that the visual isn’t sorted on an invisible column within the dataset.
These are by far the most common errors that we come across.
We hope you’ve found this article helpful!
More updates to come as Microsoft continuously pushes out new updates and introduces new features to one of the best business intelligence platforms available today.