Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

RANKX Function

Hi all, I'm trying to figure out a way to use the RANKX function to rank a sales column in my table. I do not want to rank this by a certain category but rather rank them in groups (1-10) evenly dist...
Need Help
lbendlin's avatar
lbendlin
Super User
2 years ago

consider using PERCENTILEX instead.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thanks for the reply. The PERCENTILEX function is (Table, Expression, K). Obviously I would reference my table first but what is Expression and K? How would I use this to create a 1-10 numeric range?

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      calculate for k=0.1, k=0.2, k=0.3 etc.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous 

       

      Thanks to lbendlin for the correct direction. If I understand it correctly, you can try a new column with below DAX.

      RankGroup = 
VAR vSalesAmt = 'Table'[SalesAmt]
RETURN
SWITCH(TRUE(),
vSalesAmt<PERCENTILEX.INC('Table', 'Table'[SalesAmt], 0.1),1,
vSalesAmt<PERCENTILEX.INC('Table', 'Table'[SalesAmt], 0.2),2,
vSalesAmt<PERCENTILEX.INC('Table', 'Table'[SalesAmt], 0.3),3,
vSalesAmt<PERCENTILEX.INC('Table', 'Table'[SalesAmt], 0.4),4,
vSalesAmt<PERCENTILEX.INC('Table', 'Table'[SalesAmt], 0.5),5,
vSalesAmt<PERCENTILEX.INC('Table', 'Table'[SalesAmt], 0.6),6,
vSalesAmt<PERCENTILEX.INC('Table', 'Table'[SalesAmt], 0.7),7,
vSalesAmt<PERCENTILEX.INC('Table', 'Table'[SalesAmt], 0.8),8,
vSalesAmt<PERCENTILEX.INC('Table', 'Table'[SalesAmt], 0.9),9,
10)

       

      Best Regards,
      Jing
      If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Thank you Anonymous and lbendlin for the reponses. I believe the PERCENTILEX.INC function is the right way to go. Can you explain why you use expressions 0.1, 0.2 ,0.3, etc...? When I apply this formula to my specific case I only get a rank group from 8-10.