Rank negative number result not expected

[dgdgdg122db]

I am trying to rank the customers based on the margins, however, I am facing issues with the ranking results, in particular, the unexpected ranks on negative numbers.

Without using tie Dense, 

Rank_ = RANKX(ALL('Customer EMEA '[Customer]),[Total margin],,DESC)

giving me the ranking result 88364 on the first negative number, which is not correct because my total customers are only 2788. I think it is probably ranking the negative number on my entire customer base: around 88K customers in total.

I am wondering why it gave me such results? and how can I fix it? 

I am expecting the ranking for the first negative number -0.05  is 2735

Please help.

Measures that I used:

Total margin = SUM('Customer EMEA '[Sales Margin])

Rank_ = RANKX(ALL('Customer EMEA '[Customer]),[Total margin],,DESC)

Rank Dense = RANKX(ALL('Customer EMEA '[Customer]),[Total margin],,DESC,Dense)

[v-alq-msft]

Hi, @dgdgdg122db 

Based on your description, I created data to reproduce your scenario.

Here is the Rankx function.

RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])

The default value of last parameter 'ties' is 'Skip'. For 'Skip', the next rank value, after a tie, is the rank value of the tie plus the count of tied values. In my example, one(1) values are tied with a rank of 1 then the next value will receive a rank of 4(1+3).

Whle for 'Dense', the next rank value, after a tie, is the next rank value. In my example, one(1) values are tied with a rank of 1 then the next value will receive a rank of 2.

For further information, you may refer to the following link.

https://docs.microsoft.com/en-us/dax/rankx-function-dax

Best Regards

Allan

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

[Mariusz · Accepted solution]

Hi @dgdgdg122db 

If RRANKX with Dense fixes the issue why not just use that?

Please see the RANKX function description that explains how the Dense below.

https://docs.microsoft.com/en-us/dax/rankx-function-dax

If you want to know why it accrues you will need to investigate your Model, 'Customer EMEA ' is probably a good start.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.