Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |