Bucketing Cumulative Total using DAX

[Avi]

Hi

How can I represent the following using DAX.

I am trying to Bucket the following Sales Quantity per Customer:

Customer	Quantity
A	0
A	200
A	300
B	100
C	80
C	20
D	50
D	25
D	25
D	0
TOTAL	800

and represent the following in Buckets of 3 as Top 20%, Mid 60% and Bottom 20% of the Total as below:

Bucket	Qty	Qty %
Top 20%	500	63%
Mid 60%	200	25%
Bottom 20%	100	13%
TOTAL	800	100%

Thanks in advance.

[v-qiuyu-msft · Accepted solution]

Hi @Avi,

You can create a table like below:

Table 2 = SUMMARIZE('Table1','Table1'[Customer],"Total",SUM(Table1[Quantity]))

Then create two measures below:

Qty = var MA=CALCULATE(MAX('Table 2'[Total]),ALL('Table 2'))
var Mi=CALCULATE(Min('Table 2'[Total]),ALL('Table 2'))
Return
IF(MAX('Table 2'[Total])=MA || MAX('Table 2'[Customer])="D",SUM('Table 2'[Total]),CALCULATE(SUM('Table 2'[Total]),ALL('Table 2'))-MA-Mi )

Qty% = var t=CALCULATE(SUM('Table 2'[Total]),ALL('Table 2'))
return 
DIVIDE([Qty],t)

Please downlaod attached .pbix file to have a look.

Best Regards,
Qiuyun Yu