Forum Discussion
How to Create Quintiles
Hello, I have sales data for products that I would like to categorize into quintiles to represent relative sales performance. Here is an example of my data: Product Sales $ A $127 ...
Hi Anonymous ,
Thanks for JamesFR06 answer. Based on your reply, I have some ideas that I want to share.
Below is my table:
The following DAX might work for you:
Measure = SUM('Table'[Sales]) Sales Rank = RANKX(ALL('Table'[Product]), [Measure], , DESC, Dense) Quintile = VAR _Rank = [Sales Rank] RETURN CEILING((_Rank / 7) * 5, 1) Rank Label = SWITCH([Quintile], 1, "A", 2, "B", 3, "C", 4, "D", 5, "E", "F" // Default or error case )The final output is shown in the following figure:
Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Thanks for JamesFR06 answer. Based on your reply, I have some ideas that I want to share.
Below is my table:
The following DAX might work for you:
Measure = SUM('Table'[Sales])
Sales Rank = RANKX(ALL('Table'[Product]), [Measure], , DESC, Dense)
Quintile =
VAR _Rank = [Sales Rank]
RETURN
CEILING((_Rank / 7) * 5, 1)
Rank Label =
SWITCH([Quintile],
1, "A",
2, "B",
3, "C",
4, "D",
5, "E",
"F" // Default or error case
)
The final output is shown in the following figure:
Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.