rankX Ties Returns Higher Value

[jspo]

I have a rank measure defined as 

m_Dealer Rank DESC =

RANKX(ALL('Register'[Name]),[m_GM %],,DESC)

This appears to work as intended except when we have ties.  Say we have a tie in the 4th positions the above returns 5s

1

2

3

5

5

6

8

8

9

10

In the above example is want to see 4s and 7s (skip  5 and 8 )

Thanks in advance.

Jeff

[v-yangliu-msft]

Hi  @jspo ,

I created some data:

Here are the steps you can follow：

1. Enter power query through transform data and select add column --- index column --- from 1 to generate the index

2. Create measure.

amount_measure = MAX('Table'[amount])

Rank = RANKX(ALLSELECTED('Table'),CALCULATE([amount_measure]+MAX([Index])*0.001),,ASC,Dense)

3. Result:

Best Regards,

Liu Yang

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

[jspo]

This did not solve our issue.  This is basically a 2 tier ordering.  really we need to fix the numbering to be correct so we can retain confidence in our dashboard.

Jspo

[amitchandak]

@jspo , Try with dense

m_Dealer Rank DESC =
RANKX(ALL('Register'[Name]),[m_Gross Margin %],,DESC,dense )

Refer to break ties
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

[jspo]

thanks @amitchandak but neither Dense or Skip changed the numbering sequence.

[amitchandak]

@jspo ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[jspo]

This is the best i can do at the moment.