How to Sort (Left to Right) descending by column totals in a matrix.

[tbobolz]

Hi All,

I've search and read tons but can find how to do this. Everything is about sorting on the row or column header or row totals, not the column total values. I'd like to sort the below matrix from highest column total to lowest, left to right. Hard to explain, so here is a picture. Is this possible?

Thanks for any help you can offer. 

Terry

 

[thescooble]

 why is it this method sorts from left to right when the rank column isn't even used or refereed to in the matrix?.

[lauriedata]

Because they used the "sort by column" option which happens in the background but affects the visual.

[AlexisOlson]

I don't think this is currently possible, at least not where it dynamically resorts depending on slicers/filters.

I could be wrong (someone please correct me if I am) but I can't think of a way to do this other than hacky workarounds.

[tbobolz]

Thanks, that's what I'm thinking too.

[v-easonf-msft · Accepted solution]

Hi, @tbobolz 

You can take a try follow steps:

1.calculate the 'Total' directly in fact table

Total = CALCULATE(SUM('Table'[Rxxxx]),ALLEXCEPT('Table','Table'[Hospital]))

2. Create 'Rank' column based on 'Total' filed

Rank = RANKX('Table','Table'[Total],,DESC,Dense) //or asc

3.duplicate your original column 'Hospital'

Hospital_2 = 'Table'[Hospital] 

Then sort this new column by calculated column 'Rank'

4. replace your original field 'Hospital' with new field 'Hospital_2' in matrix visual

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

This approach works as long as that the ranking is fixed. If filters or slicers are applied to the visual, there's no guarantee that this pre-calculated ranking is still accurate.