topic Re: RANKX Function in Desktop

RANKX Function

Anonymous — Thu, 11 Apr 2024 16:38:14 GMT

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 distributed across the dataset by the sales number. For example group 1 would contain the lowest sales amounts and group 10 would contain the highest sales amount. I can't seem to figure out how to accomplish this without defining a category to rank by which I don't want to do. Thanks for any replies!!

Re: RANKX Function

lbendlin — Thu, 11 Apr 2024 17:43:05 GMT

consider using PERCENTILEX instead.

Re: RANKX Function

Anonymous — Thu, 11 Apr 2024 17:54:09 GMT

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?

Re: RANKX Function

lbendlin — Thu, 11 Apr 2024 17:55:20 GMT

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

Re: RANKX Function

Anonymous — Fri, 12 Apr 2024 08:09:01 GMT

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!

Re: RANKX Function

Anonymous — Wed, 17 Apr 2024 16:35:24 GMT

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.

Re: RANKX Function

lbendlin — Wed, 17 Apr 2024 21:44:02 GMT

how much data do you have? Needs to be statistically significant.