Forum Discussion

dgdgdg122db's avatar
dgdgdg122db
Icon for Helper II rankHelper II
6 years ago
Solved

Rank negative number result not expected

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)
 
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Mariusz's avatar
    Mariusz
    6 years ago

    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.

     

2 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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.

     

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.