Forum Discussion
Matrix Column Sort Order
The article at https://community.powerbi.com/t5/Desktop/Matrix-Column-Head-Order/td-p/71572 talks about sorting by an index column. I was able to use that info to sort by a value column. It's not pretty, but it works. Here's the skinny:
- In the Data view, in your measures folder, create a calculated measure for the value you want to sort by [Sort Value] = SUM('MyTable'[Values]).
- Create a new calculated column [Sort Order] in the table that is the source dimension for your matrix column category, and make [Sort Order] = [Sort Value].
- On the "Modeling" tab of the Data view, click the "Sort by Column" button and choose [Sort Order].
- Go back to the Report view and add your matrix, adding row, column, and value fields.
- If the columns are sorted in the order you wanted, you win!
- If they're sorted in the opposite order you wanted, go back to your sort order column in your column dimension table and change the formula to [Sort Order] = - [My Value].
- Go back to the report tab, and the columns should now be sorted in the order you want.
Note that this method sorts on the total value of the value column outside the context of the matrix, so it will not dynamically change based on values in the matrix, i.e. if product A has more sales than product B last year, but Product B is the winner this year, changing year filters won't cause the order of the columns to change, they still sort by total sales. I also don't know how it behaves with ties. I'm sure there's a way to write a context sensitive measure if you needed to, but this got me where I needed to be.