Normal sorting behaviour is to sort based on a numeric value, either higher to lower or lower to higher. But what if you want to have a custom sort order, or you are trying to sort categorical data in a specific way. How do you do that? Let's look at an example.
The school our boys attend reports on their progress in some of their non-core subjects using the categories, Exemplary, Commendable, Satisfactory, Limited and Unsatisfactory
So their approach to learning in a subject may be Exemplary, and their progress may be rated Commendable.
Across a number of subjects they might end up with numbers like this across those 5 categories
Of course our children would never end up with any Unsatisfactory areas, or at least that's what they'd like us to believe
Download this Example Power BI Desktop File
Enter your email address below to download the sample file used in this post.
Watch the Video
Sort-By Columns in Power BI Steps
Loading this into Power BI I can create a column chart, which here is sorted by Value in ascending order.
But I want the values sorted by the Achievement category
However this sorts the category alphabetically
but I want to see the data in the order
How to do this? By using a Sort By column.
A Sort By column is a numeric column created in the data table that tells Power BI how to sort the categories.
To begin creating one, click on Transform Data to open the Power Query editor
Add a Custom Column
Using this code to link a numeric value to each category. Exemplary has a Sort By value of 1, Commendable is 2, etc.
This gives a new column called Sort By with values from 1 to 5
We can now tell Power BI to use this numeric column for sorting the Achievement column.
Close the Power Query editor by clicking on Close & Apply
In Fields, click on Achievement and then on the Sort By Column button
From the drop down list click on Sort By
The chart is now sorted, in ascending order, by the Achievement category.
For bonus points, click on the ellipses (More Options) for the Sort By field
and hide the field