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
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).