Forum Discussion

bartvana's avatar
bartvana
Iron Contributor
Sep 09, 2021
Solved

RANKX with multiple dimensions

I created a WeekRank column in DAX using RANKX on AvgQty, which works fine, except that if AvgQty is the same, I need the ranking to fall back to Week so that I don't get identical values in WeekRank.

So in the example below, instead of in WeekRank having 1, 1, 1, 1, 1, 6, I need 1, 2, 3, 4, 5, 6.

Ranking from 1 to 5 should be based on Week (given that AvgQty values are identical).

 

My RANKX formula is as follows (grouped by "Product"):

=RANKX (
    FILTER (
        tSeasonalProfile;
        EARLIER (tSeasonalProfile[Product]) = tSeasonalProfile[Product]
    );
    tSeasonalProfile[AvgQty];
    ;
    ASC
)

Any help would be greatly appreciated!

BI & Data Analysis
excel
Formulas and Functions
power bi
  • bartvana's avatar
    bartvana
    Sep 09, 2021

    Searching a bit further on the internet I found the answer well explained in this blog post: https://community.powerbi.com/t5/Community-Blog/Breaking-Ties-in-Rankings-with-RANKX-Using-Multiple-Columns/ba-p/918655.

    If you'd want to rank first by GPA and then by Income, you'd do like this:

    RankedByGPA(Income) = 
RANKX(ALL('Table'), 
    RANKX (ALL('Table'), 'Table'[GPA])
        + DIVIDE( 
            RANKX(ALL('Table'), 'Table'[Income]), 
            (COUNTROWS(ALL('Table')) + 1)
        )
, , ASC)

1 Reply

  • bartvana's avatar
    bartvana
    Iron Contributor
    Sep 09, 2021

    Searching a bit further on the internet I found the answer well explained in this blog post: https://community.powerbi.com/t5/Community-Blog/Breaking-Ties-in-Rankings-with-RANKX-Using-Multiple-Columns/ba-p/918655.

    If you'd want to rank first by GPA and then by Income, you'd do like this:

    RankedByGPA(Income) = 
RANKX(ALL('Table'), 
    RANKX (ALL('Table'), 'Table'[GPA])
        + DIVIDE( 
            RANKX(ALL('Table'), 'Table'[Income]), 
            (COUNTROWS(ALL('Table')) + 1)
        )
, , ASC)

Resources