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
    1 year 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