How to Create Quintiles

[Kyle_Buffin]

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
B	$501
C	$690
D	$1835
E	$399
F	$32
G	$479

My actual data spans for hundreds of thousands of products, and sales dollars in excess of $1M. I would like to create quintiles to break down products by performance. I would like to have the top products that compose 20% of our total sales as "A" rank, the next 20% of sales $ and their products as "B" rank, and so on down to "E" rank. To clarify, I don't want 20% of products in each quartile, I want 20% of sales $ in each quartile, and the rank will be assigned to the products based on their contribution towards total sales dollars. Is this possible within PowerBI?

Thank you!

[Anonymous · Accepted solution]

Hi @Kyle_Buffin ,

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.

[JamesFR06]

 to foloow the subject. I've tried and do not succeed