Forum Discussion

viswaaa's avatar
viswaaa
Icon for Helper IV rankHelper 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

sashams172's avatar
sashams172
Frequent Visitor
7 hours ago

This simple process works for me ... say you have fields called 'category' and 'sub-category' in Rows of a matrix visual, and a 'measure' in Values.
1. Change the matrix to a table visual.

2. Remove sub-category.

3. Sort the category and measure as per your preferred order holding shift key.

4. Change the table to matrix.

5. Add the sub-category back to Rows under category.

This results in maintaining sort order on both category (1st sort order) and measure (2nd sort order) thus keeping the sub-category in right order.

Thanks,

Salman