Forum Discussion

Chrisjr's avatar
Chrisjr
Icon for Helper IV rankHelper IV
2 years ago

Rankx with ties

Hello All, 

 

I have a ranking issue I can't solve. 

 

I have the below table: 

1 same group can have multiple dealers. 

 

My ranking formula is  the following: 

ranking test = IF(NOT (ISBLANK([Score in %])),CALCULATE(RANKX(ALLSELECTED(Lookup__Dealer), [Score in %], , DESC)), BLANK())
 
 
This works well. However, when I add the Region  column (coming from a separate dim table), it gives me ties:

 


Suddenly, the dealer who was number 1 became number 2 and the number 2 and 3 both became number 3. Therefore, if in my slicer I select bottom 1, it will show me blank value.

 

 

I need the bottom 1 to remain 1 when I add the Region column like as below:

N GroupDealer Full NameRanking testscore in %Region
125.......7371,11FR
573.......6262,22NL
573........2149,44NL

 

And If I select the Region NL in a slicer, I need my ranking to adapt iself as below:

N GroupDealer Full NameRanking testscore in %Region
573.......6262,22NL
573........2149,44NL

 

 

How could I achive this ?

 

 

Thanks

"RANKX"

1 Reply

  • talespin's avatar
    talespin
    Icon for Solution Sage rankSolution Sage
    2 years ago

    hi Chrisjr 

     

    Please try something like this, correct the table/column names. If it doesn't work, please share sample data, few rows from tables involved in this visual(remove any sensitive information).

    RANK( DENSE, ALLSELECTED(Lookup__Dealer), ORDERBY( [Score in %], DESC))