Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Denis_Slav
Helper III
Helper III

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. 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super 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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

1 REPLY 1
amitchandak
Super User
Super 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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!