Forum Discussion

ViralPatel212's avatar
ViralPatel212
Icon for Resolver I rankResolver I
2 years ago
Solved

RankX - Unique ranking for Duplicate Values

Hello Team,

 

I am trying to create a DAX ranking measure that I can add to a table which ranks the Dealers based of the Volume.

The problem i am having here is that "Flow Traders" and "Santanders" are tied at 16 as their volume is 2.80 and the same goes for "ING" and "Mizuho" also tied at rank 22 as their volume 0.60. in this each of the dealers should have its own unique number 1-26 in this example.

MEASURE:

 

Dealer Ranking Number 2 = 

VAR _volume = [Ranking Size (Vol)]

RETURN

  IF( [Ranking Size (Vol)] = blank(), BLANK(),  RANKX(ALLSELECTED(cp[counter_party]),[Ranking Size (Vol)],,DESC,Dense))

 

Please see the Sample File here:

Sample file 

Thanks

Viral

Need Help
Show and Tell
Tutorial Requests

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Your solution is worked amitchandak
    Hi, ViralPatel212 

    Based on the screenshot information you provided, I can't download your report because I can't download it. I've created this example data below: 

    There will indeed be duplicates using the formula you provided:

    The expressions provided by Super User work perfectly:

    I've provided the PBIX file used this time below.

     

     

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    ViralPatel212 , use new rank or rownumber function

     

    Rank

    RANK(DENSE, ALLSELECTED(cp[counter_party]),ORDERBY([Ranking Size (Vol)],DESC,[counter_party]) )

     

    Rownumber

    ROWNUMBER(ALLSELECTED(cp[counter_party]),ORDERBY([Ranking Size (Vol)],DESC))

     

    Power BI - New DAX Function: RANK - How It Differs from RANKX: https://youtu.be/TjGkF44VtDo

    Rownumber- https://www.youtube.com/watch?v=yS9-IQjUDwg&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L&index=1

  • suparnababu8's avatar
    suparnababu8
    Icon for Super User rankSuper User
    2 years ago

    Hi ViralPatel212 

    To ensure each dealer has a unique rank, you can use the RANKX function with the DENSE ranking method and add a tie-breaking condition. Here’s an updated version of your DAX measure:

     

    Dealer Ranking Number  =
    VAR _volume = [Ranking Size (Vol)]
    RETURN
    IF(
    ISBLANK(_volume),
    BLANK(),
    RANKX(
    ALLSELECTED(cp[counter_party]),
    _volume,
    ,
    DESC,
    DENSE
    ) +
    RANKX(
    ALLSELECTED(cp[counter_party]),
    cp[counter_party],
    ,
    ASC,
    DENSE
    ) / 1000
    )


    In this measure:

    1. The first RANKX function ranks the dealers based on their volume in descending order.
    2. The second RANKX function adds a small fractional value based on the alphabetical order of the dealer names to break ties.

    This should give each dealer a unique rank. Replace cp[counter_party] and [Ranking Size (Vol)] with the actual column names in your table.

     

    Thanks!