Nested sort in Matrix visual

[viswaaa]

I have a matrix visual like below and first need to sort by category and then by sales for each category top to bottom.

How to do this please suggest.

I cannot use a table for this only matrix I need to use

I took category ,location in rows and year in columns and sales in values

[v-dineshya]

Hi @viswaaa ,

Thank you for reaching out to the Microsoft Community Forum.

Please try below two options.

1. Created Sorting measure to calculate total sales across years.

Total sales = SUM(FactTable[Sales])

In Matrix visual, Drag Category and Locations in Rows, Year in Columns and Sales in Values. Right click on Location in Rows header --> choose Sort by column--> select the sorting measure (Total Sales)

2. Created Rank measure.

Location Rank =
RANKX(
FILTER(ALLSELECTED(DimLocation), DimLocation[Category] = MAX(DimLocation[Category])),
,
DESC,
DENSE
)

In Model View, set Location to be sorted by Location Rank. By this within each category locations will appear sorted by sales.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[alish_b]

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!

[viswaaa]

Hi @alish_b ,

Thanks for your quich help.

But after I convert my table to matrix its missing the sorting again.

[alish_b]

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

[rohit1991]

Hi @viswaaa 

In Power BI, a Matrix can only sort the top level (like Category). It does not support dynamic nested sorting of the next level (like Location inside Category) by values such as Sales.

If you need Category sorted and then each Location sorted by Sales (highest to lowest), this isn’t possible directly in a Matrix. The only options are:

• Use a Table visual (it lets you sort by any measure column).

• Or create a helper rank column to give a fixed order inside each Category, but that won’t change dynamically with slicers.

So, Matrix doesn’t support nested sort by measure, only predefined sort.