I’m sure many people have used the sorting capability in Power BI, whether it be sorting by a column you’re using in the visual or sorting with the column tool (sort by another column). And they are super useful in many occasions!
But there are also times where you want to sort values that have some hierarchy. How can you sort them in the way that the parent values are sorted ascending, but the child values are sorted descending? Things can get complicated pretty quickly but there is a solution!
This is actually one of the problems I helped to solve at work. There are definitely other solutions but here is a solution I came up with.
Let’s say, you have this table containing product, type and value columns. This is what it looks like as is.
What if you wanted to modify this matrix visual so that the data is sorted alphabetically descending by product, and also by type as such that its order will be “Medium”, “Big”, and “Small”. There is no way to sort by multiple columns. So what’s the solution?
A solution is to create an index for each column in the hierarchy.
All you have to do is to add index columns to each of your parent and child categories so that each column in the hierarchy will be sorted as you want when sorting by another column in the column tools. You may need to be creative in adding your index column if you want your data to be dynamic.
And you get…
We got it sorted as we wanted! Hope this post will help somebody in some way 😀
Download file here: Link