Sales Volume Contribution Grouping (Similar to Quartiles)

[Mainer04401]

I’m trying to do something similar to calculating percentile quartiles but instead of 25% of customers I want each “quartile” to consist of 25% of sales.  So, for example, I may see that the top 25% of sales volume comes from just 5 stores while the bottom 25% of sales volume comes from 100 stores.    

In Excel, I would sort the stores by sales volume in descending order, then add a column for cumulative contribution and a blank column for volume contribution bucket.  Stores with <= 25% cumulative contribution are labeled “Top 25% of Sales” in the volume contribution bucket column, stores with > 25% to <= 50% cumulative contribution are labeled “> 25% to <= 50% of Sales”, etc

Is there a way to do this in DAX / PowerBI? 

Below is an example from Excel:

Customer	Division	Sales	Cumulative Sales	Cumulative % of Total Sales	Grouping
A	East	250	250	0.25	Top 25% of Sales
B	Wes	100	350	0.35	Upper-Middle 25% of Sales
C	Central	150	500	0.5	Upper-Middle 25% of Sales
D	East	120	620	0.62	Lower-Middle 25% of Sales
E	Wes	70	690	0.69	Lower-Middle 25% of Sales
F	Central	60	750	0.75	Lower-Middle 25% of Sales
G	East	60	810	0.81	Bottom 25% of Sales
H	Wes	55	865	0.865	Bottom 25% of Sales
I	Central	50	915	0.915	Bottom 25% of Sales
J	East	40	955	0.955	Bottom 25% of Sales
K	Wes	25	980	0.98	Bottom 25% of Sales
L	Central	20	1000	1
		1000

[Mainer04401 · Accepted solution]

After lots of trial and error, I figured out a way to do it.  

I created 3 new columns:

Cumulative Sales =
calculate([Sales Measure], FILTER('Store List',

'Store List'[Sales] <= EARLIER('Store List'[Sales])))

All Sales Column =

calculate([Sales Measure],all('Store List'))

Sales Quartile =
if('Store List'[Cumulative Sales] <= (.25*[All Sales Column]),"Bottom Quartile",

if('Store List'[Cumulative Sales] > (.25*[All Sales Column]) && 'Store List'[Cumulative Sales] <= (.50*[All Sales Column]),"3rd Quartile",

if('Store List'[Cumulative Sales] > (.50*[All Sales Column]) && 'Store List'[Cumulative Sales] <= (.75*[All Sales Column]),"2nd Quartile","Top Quartile")))

This groups the stores into quartiles, each contributing 25% of sales, from least to greatest.

[v-yuta-msft]

@Mainer04401 ,

You may try PERCENTILE.EXC function, please refer to blog below:

https://sqldusty.com/2018/08/31/calculating-quartiles-with-dax-and-power-bi/

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mainer04401]

The problem is that percentile puts the same percent of stores in each quartile group.  I'm looking for a function that puts the same percent of sales volume in each quartile group.  

[v-yuta-msft]

@Mainer04401 ,

Could you please clarify more details about "Sales volume" you mentioned?

Regards,

Jimmy Tao

[Mainer04401]

@v-yuta-msft- Sales volume is total quantity of units sold.  I am trying to group stores by contribution to sales, top 25% of sales, upper-middle 25% of sales, lower-middle 25% of sales and bottom 25% of sales.  See my original post for details on how I would group the stores in Excel.  I'm trying to build this into PowerBI so that I can filter by geography and the quartile will update accordingly, i.e. stores that contribute the top 25% of sales in the filtered geography.  Thanks.