Forum Discussion
Sorting Matrix based on column in descending order
Good morning,
I have the below matrix:
This matrix show the top 5 shops based on total sales, filtered by Category and product.
Is there any way to sort the columns from best selling shop to lowest selling shop (within the top 5).
In this case the order should be Shop 4, Shop 2, Shop 3, Shop 5, Shop 1
I know this isn't possible within the sorting options of the matrix. But maybe there is a workaround?
2 Replies
One way to achieve this sorting within the matrix is to create a calculated column that ranks the sales of each shop, and then use that column as the sorting mechanism for the matrix.
Here's the DAX code for the calculated column:
Ranking = RANKX(ALL(Shops), [Total Sales])
You can then sort the matrix by this calculated column in descending order.
Note: In this case, "Shops" is the table that contains the shops and "Total Sales" is the measure that calculates the sales for each shop.
That is working fine, until I use slicers in the report. When I use slivers in the report, such as Year,Month, Week, ShopChain, ...) then the sorting is again wrong.
When I use the ranking formula in a measure, it is working fine. However, I can't use a measure in the columns of a matrix...
