Forum Discussion

viswaaa's avatar
viswaaa
Helper IV
1 year ago
Solved

Nested sort in Matrix visual

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 Help
  • v-dineshya's avatar
    v-dineshya
    11 months ago

    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

rohit1991's avatar
rohit1991
Super User
1 year ago

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.