Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Trouble with RankX

Objective: Have a column presenting the rank of customer based on total sales in descending order

 

Data Format:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

What has not worked:

Ranking =
RANKX(
ALLSELECTED(Table_1046),
CALCULATE(SUM(Table_1046[Extended Price]))
,,DESC
)
 
Also tried:
Rank v2 = IF(HASONEVALUE(Table_1046[Billing Customer Number]), RANKX(ALL(Table_1046[Billing Customer Number]), [Parts Sales]) )
 
Resulted in: 
 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Definitely feeling like an idiot here as this doesn't seem like this should be as challenging as it has turned out to be

 

Need Help

8 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

    Anonymous there is detail blog post on RANK, here is link of 1 of 3. hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you for the link.

       

      Should I treat the Billing Customer Number as the subcategory then? Attempting based on that does seem to rank them, but not quite like I'd imagined.

       

      DAX attempted:

       

      Rankv4 = RANKX(FILTER(Table_1046, Table_1046[Billing Customer Number]=EARLIER(Table_1046[Billing Customer Number])), SUM(Table_1046[Extended Price]))
       
      Result: 

       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
      It seems to be working to some extent, but I need to figure out why the numbers are jumping around like that. I tried adding the ASC or DESC to the DAX statement, but it did not change anything. Perhaps I'm putting it in the wrong place?
       
      Rankv4 = RANKX(FILTER(Table_1046, Table_1046[Billing Customer Number]=EARLIER(Table_1046[Billing Customer Number])), SUM(Table_1046[Extended Price]),,ASC)
       
      Rankv4 = RANKX(FILTER(Table_1046, Table_1046[Billing Customer Number]=EARLIER(Table_1046[Billing Customer Number])), SUM(Table_1046[Extended Price]),,DESC)
      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        7 years ago

        Anonymous share the sample data in excel using one drive/google drive and i will send you the solution. Remove any sensitive information.