Sort Power BI matrix by any column
Matrixes are a powerful way of visualizing tabular data. Often, we find ourselves preferring a matrix over a table, given its high degree of flexibility. Nonetheless, it occurs our customers ask for functionalities that do not belong to the common nature of matrixes.
In this blog post, we are going to show you how to sort a Power BI matrix by a column of your choice.
You have a matrix that shows products sales over months:
You receive the request to sort the matrix by the total sales recorded in the current month. Because the header or a matrix column is in reality the row value a table, this option is not available. How do we get around this?
1. Enable totals
You can enable totals by toggling the column subtotals in the Format Pane of your matrix.
2. Adjust the total calculation of your measure
We can leverage on DAX to create a measure that assigns the amount of sales YTD to the Total column.
This is how it looks like:
3. Sort your matrix by the total column
You can click on the arrow right below the column header of your Total column. This action will allow you to sort the matrix by the sales YTD.
4. Hide the total
Now, you can hide the totals again and, there you go! Now your matrix is filtered by a column of your choice.