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
- Exemplary
- Commendable
- Satisfactory
- Limited
- Unsatisfactory
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
Excel Pyramid Charts
Uli
Hi Phil, thanks for this blog post. It is very useful for my PowerBI reports but I also use the data of the report (not the visual) directly in Excel via PowerPivot (some users just don’t like PowerBI). Do you have a recommendation for a workaround of this “Sorty by” in Excel? I have a “Sort by” column on hand but I do not have a clue how to tell Excel to use this sorting in my PivotTable. I use to just drag and drop the rows, but I don’t feel that’s sustainable. Thanks and appreciate your work here! Best wishes, Uli
Philip Treacy
Hi Uli,
Open File->Options->Advanced in Excel and scroll down to General. You should see a button that reads Edit Custom Lists … – click on this.
You can now add a list i.e. Exemplary, Commendable etc
Click OK to save and then get back into the Excel worksheet.
Right click in your Pivot Table and choose Pivot Table Options.
On the Totals & Filters tab, at the bottom, make sure Use Custom Lists when sorting is checked. Click OK to save.
Your Pivot table should now sort according to the order of the words in the custom list you just created.
Regards
Phil