How to sort account names within fixed category order in matrix visual?

[Chateauunoirr]

Hello everyone,

I could use some help with a sorting issue in Power BI.

I have three tables structured as follows:

In my matrix visual, I’ve placed:

• Rows: Category Name → Account Name

• Values: Sum of Price, Sum of Time Spent

I’ve already sorted the Category Name column in the Categories table using the Category Rank column, which gives me the desired order:

“Big Buyers”, “Medium Buyers”, “Small Buyers”

However, when I try to sort the matrix by Price or Time Spent, the sorting affects the Category Name level as well — it reorders the categories depending on the measure.

What I’d like instead is to keep the categories in the fixed order defined by Category Rank, and only sort the Account Names within each category based on the selected measure (Price or Time Spent).

Is there a way to achieve this behavior in the matrix visual?

Thank you in advance for your help!

[danextian · Accepted solution]

Hi @Chateauunoirr 

That’s exactly how the matrix visual works — you can sort by either the row category or the measure, but not both at the same time. I covered a workaround for this in my vlog: https://youtu.be/FWlDFVNis08

Just a heads-up: it can be a bit finicky and might not be ideal for beginners or anyone looking for a quick fix.

[Chateauunoirr]

Hi @danextian 
Thanks for the solution

The solution does take time to implement, but it's still a superb video solution. I'll need to sit down and understand this.

Can we apply the logic to two fields at the same time by assigning a rank to two columns? It seems a bit complex to me, but I wanted to get your opinion.

[danextian]

Hi @Chateauunoirr 

If the columns are form the same table, you can use

ALL ( 'dimTable'[column1], 'dimTable'[column2] )

[FBergamaschi]

As far as I know it is not possible as of today

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI