Forum Discussion
Nested sort in Matrix visual
Hi viswaaa ,
Please try below steps.
1. Create measure for total sales.
Total Sales = SUM(FactTable[Sales])
2. Create a rank measure to sort each location within its category.
Location Rank =
RANKX(
FILTER(ALLSELECTED(DimLocation), DimLocation[Category] = MAX(DimLocation[Category])),
CALCULATE([Total Sales]),
,
DESC,
DENSE
)3. In Matrix visual, Drag Category and Location in Rows, Year in Columns and Sales in Values.
4. In Model view --> select Location Column --> In ribbon, use Sort by Column --> choose Location Rank.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Hey viswaaa ,
I am assuming you are asking for a nested sort which first sorts by the Category in alphabetical order (A to Z or Z to A) and then sorts using sales (and I am hoping this is the Sum of sales of both 2024 and 2025) in either ascending or descending order.
Basically, a combined sort as shown in the image below:
The above was achieved by first building a table and then converting it into a matrix. This was done because only table supports nested sorting using Shift+Click (first click on the Category once or twice depending on whether you want it in ascending or descending, and then shift+click on another field once or twice as per sorting order needs and you can actually continue this to build a multi-layered nested sort) and this is retained when converting into a matrix.
Now there is no programmatic way to set this up at the moment and you will have to do this manually but if you are using Service for end user experience (or just export as PDF) this serves as a default view so it should work fine.
Hope it helps!
Hi alish_b ,
Thanks for your quich help.
But after I convert my table to matrix its missing the sorting again.
Hello viswaaa ,
That is unfortunate. This was somewhat of a volatile solution as the sorting could be lost when you click on any other sortable column and further testing I am wondering if this only supports nested sorting for two fields.
If you have sample data in a format that can be copied, please do share it here with a sketch/excel mockup of what your result should look like and I could check if such a solution could be achieved (will still have the limitations as above though). And I know you mentioned in the post that you did not want a table visual but many a times Power BI limitations are such a pain that we really cannot reach an expected output without a compromise so please try to communicate the limitations with proposed solutions (in my experience, many clients have been quite accepting of this).