Forum Discussion

snandy2011's avatar
snandy2011
Helper IV
7 years ago

Problem with rankx function for bottom

Hi,

I just want to calculate bottom customer but having little problem with my DAX.

 

The scenario is that, I have a slicer with 3 values (Profit,Profit Margin,Sales).and on my another slicer where i have created bottom 5,bottom 10 etc. when i select Sales and bottom 5 it's only shows 4 value.but, if i choose profit or profit margin and bottom 5 or 10, strangely it is showing correct value.

 

I don't know how it is possible?

 

Here is my list of  formula,

 

Rank from bottom = RANKX(ALL('Top Analysis Unpivot'[Value]),'Top and Bottom Analysis Measures'[Dynamic],,ASC,Dense)

 

Rank Selection for Bottom = IF(HASONEVALUE('Ranking For Bottom'[Rank] ),VALUES('Ranking For Bottom'[Rank Number]),MAXX(VALUES('Top Analysis Unpivot'[Value]),[Rank from bottom]))      [ This formula selects the rank number from my rank slicer)

 

Bottom  value = IF([Rank from bottom] <=[Rank Selection for Bottom],[Dynamic])    [ This formula shows the ultimate value]

 

please Have a look on the screenshots

 

But when i choose profit from the slicer it works fine

 

Can you identify what is going on? why it is showing only 4 values instead of 5?

 

Any suggesation is really appreciable.

 

Thanks,

snandy

 

 

 

Need Help

5 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi snandy2011,

     

    Do you have any repeat values when maing the rankx? meaning that instead of having 1, 2, 3, 4, 5,6 you have something like 1,2,3,4,6,6?

    Seems to me like you have repeated ranking that causes to reduce number of values to present.

     

    Search your data if any of the top/bottom 5 have more than 1 customer for the same ranking.

     

    Regards,

    MFelix

     

     

    • snandy2011's avatar
      snandy2011
      Helper IV
      7 years ago

      MFelix,

       

      Nope.. I don't think so.. 

       

      Check out the below screenshots,

       

       

      It Starts with rank 2..I dont understand why?

       

      But for profit or other measure, it shows correct rank.

       

      Can you please identify what is happening?

       

      Thanks for replying.

       

      snandy

      • MFelix's avatar
        MFelix
        Super User
        7 years ago

        Hi snandy2011,

         

        Starting the rank in 2 is the reason you have only 4 numbers, does for any chance for the 21.78 do you have two rows on your dataset that are exactly the same?

         

        Regards,

        MFelix