Roll up Calculations

[mattkarriker]

Hello Forum

Having an issue and I am not sure how to complete.  I am looking for a way to compute order fill rate at different levels.  The first level is at the line level.  I need to compute the percentage that the line was filled.  I get this pretty easily.  I then need to score the line as 100% filled or not with a calculation to return yes or no.  At the order level I need to know how many lines were 100% filled / total order lines for a percentage of lines filled.  This is where I run into issues.  Below is a simplified view of the data and attempts as the needed calcs.  Any suggestions?  Thank you in advance for your help. 

Order Number	Line Number	OrderLine_key	Cancelled Qty	Shipped Qty	Backordered Qty
1	1	1-1		2
1	2	1-2	2	2	1
1	3	1-3		5
1	4	1-4	2
Calcs
Order Qty = Cancelled Qty + Shipped Qty + Backordered Qty
Line Fill Rate = Shipped Qty/Order Qty
Line Filled = If Line Fill Rate = 1 then Yes else No
Order Line Fill Rate = Cacluate(DistinctCount(Line_Key),Line Filled = Yes)/DistinctCount(Line_Key)
Order Number	Line Number	OrderLine_key	Cancelled Qty	Shipped Qty	Backordered Qty	Order Qty	Line Fill Rate	Line Filled
1	1	1-1		2		2	1	Yes
1	2	1-2	2	2	1	5	0.4	No
1	3	1-3		5		5	1	Yes
1	4	1-4	2			2	0	No
Order Number	Order Line Fill Rate
1	0.5

[v-qiuyu-msft · Accepted solution]

Hi @mattkarriker,

In your scenario, please create calculated columns like below:

Oder Qty = Table1[Cancelled Qty] +Table1[Shipped Qty] +Table1[Backordered Qty]

Line Fill Rate = DIVIDE(Table1[Shipped Qty],Table1[Oder Qty],0)

Line Filled = IF(Table1[Line Fill Rate]=1,"Yes","No")

Then create a measure like this:

Order Line Fill Rate = DIVIDE(CALCULATE(DISTINCTCOUNT(Table1[OrderLine_key]),FILTER(ALL(Table1),'Table1'[Line Filled]="Yes")),CALCULATE(DISTINCTCOUNT(Table1[OrderLine_key]),ALL(Table1)))

You can download attached .pbix file to have a look.

Best Regards,
Qiuyun Yu