Hello there, I'm struggling with something and I cannot find the answer for the life of me. Hoping someone else might be able to help.
In a Matrix, I want to show the rank of a measure over the combination of two columns. In this case ,we have Branches and Teams. Each branch has a Team 1, Team 2, etc.
So then the unique combinations are what I want to rank on, but in the matrix I want to be able to still break out Branch, Team, and then the rank.
However I write this, when I bring the two columns over to the matrix and add the measure, it ranks by the outer column first, then the inner column.
I'm attaching a table below of the results I get vs what I wish to get.
BRANCH | TEAM | VALUE | DAX RANK | DESIRED RANK |
BRANCH C | TEAM 3 | 972 | 1 | 1 |
BRANCH G | TEAM 3 | 914 | 1 | 2 |
BRANCH C | TEAM 1 | 894.5 | 2 | 3 |
BRANCH E | TEAM 2 | 882.5 | 1 | 4 |
BRANCH B | TEAM 2 | 822 | 2 | 5 |
BRANCH F | TEAM 1 | 754.5 | 1 | 6 |
BRANCH F | TEAM 3 | 697 | 2 | 7 |
BRANCH B | TEAM 1 | 573.5 | 2 | 8 |
BRANCH A | TEAM 1 | 440.5 | 1 | 9 |
BRANCH A | TEAM 3 | 399.5 | 2 | 10 |
BRANCH E | TEAM 3 | 368 | 2 | 11 |
BRANCH C | TEAM 2 | 267 | 3 | 12 |
BRANCH A | TEAM 2 | 254.5 | 3 | 13 |
BRANCH D | TEAM 3 | 186.5 | 1 | 14 |
BRANCH E | TEAM 1 | 144 | 3 | 15 |
BRANCH G | TEAM 2 | 130.5 | 2 | 16 |
BRANCH G | TEAM 1 | 104 | 3 | 17 |
BRANCH D | TEAM 2 | 55.5 | 2 | 18 |
BRANCH B | TEAM 3 | 30.5 | 3 | 19 |
BRANCH D | TEAM 1 | 15 | 3 | 20 |
BRANCH F | TEAM 2 | 2 | 3 | 21 |
This is the base of the DAX:
RANKX( ALLSELECTED( Table[Branch], Table[Team] ), [Measure], , DESC )
(If I concatenate the columns and rewrite the measue using that new column and put that on the matrix instead, that works, but it is not great to look at so I am wondering if what I am trying to do is even possible).
Any help is appreciated. If there's just no way, that's fine too then I can at least stop trying!
