Forum Discussion

Denis_Slav's avatar
Denis_Slav
Helper III
3 years ago
Solved

Combining RANK for Category & Subcategory

Please, help to resolve the issue. 

I've got a table, like 

L1L2ProductValue
BaBa13930
AfAf13123
BgBg12921
AgAg13912
AbAb1306
BgBg12630
AdAd10018
BcBc11137
BhBh11622
BgBg1101
BgBg12732

 

I need to calculate combined rank as (RankL1 * 100 + RankL2) for future sorting

L1L2MeasureRank
Bg84101
Bc37102
Ba30103
Bh22104
Af23201
Ad18202
Ag12203
Ab6204

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 + _RankL2

In 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

  • 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