Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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
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!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.