Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

RANX returns same ranking for two different values

This is my table 

Customer     Sales
A6
B13
D5
E7
R8
G2
A9

 

This is what I get from the DAX below :

=RANKX(ALL(Sheet1),Sheet1[Totalsales])

where [Totalsales] is another measure with the calculation =SUM(Sheet1[Sales])

Why duplicates here? please help

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    If you are creating a measure for Rank, please use this - 

    Rank Measure = RANKX(ALL('Table'[Customer]),CALCULATE(SUM('Table'[Sales])),,DESC)

     

     
    Else, if you want to create a column, first need to create another column for total sales. 
    Total Sales = CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Customer]=EARLIER('Table'[Customer])))
    Rank Column = RANKX('Table','Table'[Total Sales],,desc)
     

     

     

9 Replies

  • Thejeswar's avatar
    Thejeswar
    Super User
    4 years ago

    Hi Anonymous ,

    You need to modify your RANKX as follows

     

     

    RankMeasure = RANKX(ALL('Table'[Customer]), CALCULATE(SUM('Table'[Sales])),,DESC)

     

    . Here RANKX is written inside a calculated Measure

    If you don't want to see the Rank in Total row, use the below DAX

    RankMeasure = IF(HASONEFILTER('Table'[Customer]), RANKX(ALL('Table'[Customer]), CALCULATE(SUM('Table'[Sales])),,DESC))

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    If you are creating a measure for Rank, please use this - 

    Rank Measure = RANKX(ALL('Table'[Customer]),CALCULATE(SUM('Table'[Sales])),,DESC)

     

     
    Else, if you want to create a column, first need to create another column for total sales. 
    Total Sales = CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Customer]=EARLIER('Table'[Customer])))
    Rank Column = RANKX('Table','Table'[Total Sales],,desc)
     

     

     
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous .
    Try the below DAX;
    Rank=RANKX(Table,Table[TotalSales],,DESC)

    Thanks,
    Sanket.


    If this post helps you, then please mark it as 'Accept as Solution' and give it a thumbs up.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Anonymous  -I get '1' for all ranks

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Can you try this measure,

        Rank = RANKX(ALL('Table1'), CALCULATE(SUM(Table1[TotalSales])),,DESC)

         

         

  • Viswanathan123's avatar
    Viswanathan123
    New Member
    2 years ago

    i have 4 columns named customername, department, country and profit. i am trying to rank based on profit it is giving me unique ranks for the whole table . What i need help with is if i am filtering a department named finance it contains 20 rows i need the ranks to be created new for that department based on the profit not the ranks from the whole table for the filtered department alone i need the ranks to start from begining any suggestions for that.
    can we use RANKX inside another RANKX?

     

    • Thejeswar's avatar
      Thejeswar
      Super User
      2 years ago

      Hi Viswanathan123 ,

      If you are using the ALL() function, the RANK will not be recalculated for the slicer selection. Instead use the below DAX using ALLSELECTED() DAX function

      Rank = RANKX(ALLSELECTED('Table'), [TotalProfit],,DESC,Dense)
      Before applying filter on department

       

       
      After applying filter on department

       

      Regards,