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 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
Hi v-dineshya ,
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)
I dont see choose Sort by column option for rows.I am using pro
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 @viswaaa ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
Hi @viswaaa ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh