Roll up Calculations

Question

Hello ForumHaving an issue and I am not sure how to complete. &nbsp;I am looking for a way to compute&nbsp;order fill rate at different levels. &nbsp;The first level is at the line level. &nbsp;I need to compute the percentage&nbsp;that the line was filled. &nbsp;I get this pretty easily. &nbsp;I then need to score the line as 100% filled or not with a calculation to return yes or no. &nbsp;At the order level I need to know how many lines were 100% filled / total order lines for a percentage of lines filled. &nbsp;This is where I run into issues. &nbsp;Below is a simplified view of&nbsp;the data and attempts as the needed calcs. &nbsp;Any suggestions? &nbsp;Thank you in advance for your help.&nbsp;&nbsp;Order NumberLine NumberOrderLine_keyCancelled Qty&nbsp;Shipped QtyBackordered Qty&nbsp;&nbsp;&nbsp;111-1&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;121-2221&nbsp;&nbsp;&nbsp;131-3&nbsp;5&nbsp;&nbsp;&nbsp;&nbsp;141-42&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Calcs&nbsp;Order Qty = Cancelled Qty + Shipped Qty + Backordered QtyLine Fill Rate = Shipped Qty/Order QtyLine Filled = If Line Fill Rate = 1 then Yes else NoOrder Line Fill Rate = Cacluate(DistinctCount(Line_Key),Line Filled = Yes)/DistinctCount(Line_Key)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Order NumberLine NumberOrderLine_keyCancelled Qty&nbsp;Shipped QtyBackordered QtyOrder QtyLine Fill RateLine Filled111-1&nbsp;2&nbsp;21Yes121-222150.4No131-3&nbsp;5&nbsp;51Yes141-42&nbsp;&nbsp;20No&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Order NumberOrder Line Fill Rate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10.5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

v-qiuyu-msft · Accepted Answer

Hi mattkarriker,
&nbsp;
In your scenario, please create calculated columns like below:
&nbsp;
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")
&nbsp;
Then create a measure like this:
&nbsp;
Order Line Fill Rate = DIVIDE(CALCULATE(DISTINCTCOUNT(Table1[OrderLine_key]),FILTER(ALL(Table1),'Table1'[Line Filled]="Yes")),CALCULATE(DISTINCTCOUNT(Table1[OrderLine_key]),ALL(Table1)))
&nbsp;

&nbsp;
You can download attached .pbix file to have a look.
&nbsp;
Best Regards,Qiuyun Yu

Forum Discussion

Roll up Calculations

1 Reply

Related content

Rolling accumulation

Rolling accumulation

Rolling 12 Month calculation

Rolling 12 Column Monthly Calculation

Rolling Calculation

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