Forum Discussion
Combining RANK for Category & Subcategory
Please, help to resolve the issue.
I've got a table, like
| L1 | L2 | Product | Value |
| B | a | Ba139 | 30 |
| A | f | Af131 | 23 |
| B | g | Bg129 | 21 |
| A | g | Ag139 | 12 |
| A | b | Ab130 | 6 |
| B | g | Bg126 | 30 |
| A | d | Ad100 | 18 |
| B | c | Bc111 | 37 |
| B | h | Bh116 | 22 |
| B | g | Bg110 | 1 |
| B | g | Bg127 | 32 |
I need to calculate combined rank as (RankL1 * 100 + RankL2) for future sorting
| L1 | L2 | Measure | Rank |
| B | g | 84 | 101 |
| B | c | 37 | 102 |
| B | a | 30 | 103 |
| B | h | 22 | 104 |
| A | f | 23 | 201 |
| A | d | 18 | 202 |
| A | g | 12 | 203 |
| A | b | 6 | 204 |
I've created measure:
VAR _RankL1 =
RANKX(
ALLSELECTED( '10-01 DS_Invoices'[EventTypeL1] ),
CALCULATE( [00-01-1 Charge Total [Local]]] , ALLEXCEPT( '10-01 DS_Invoices', '10-01 DS_Invoices'[EventTypeL1] ) ),
,
DESC,
Dense
)
VAR _RankL2 =
RANKX(
ALLSELECTED( '10-01 DS_Invoices'[EventTypeL2] ),
CALCULATE( [00-01-1 Charge Total [Local]]] ),
,
DESC,
Dense
)
RETURN
_RankL1 * 100 + _RankL2In one PBIX, it's calculate correctly, but in other, it's calculate same RANK for both category A & B, and I don't understand, where could be mistake.
Denis_Slav , Hope you are using only these columns. Rankx is very sensitive to the column in the visual. If you add any other not summarized column the calculation will change. if they are the same column from another table it will not work
check if new rank function can help
New DAX Function: RANK - How It Differs from RANKX: https://youtu.be/TjGkF44VtDo
1 Reply
- amitchandakSuper User
Denis_Slav , Hope you are using only these columns. Rankx is very sensitive to the column in the visual. If you add any other not summarized column the calculation will change. if they are the same column from another table it will not work
check if new rank function can help
New DAX Function: RANK - How It Differs from RANKX: https://youtu.be/TjGkF44VtDo