Forum Discussion

GMadd's avatar
GMadd
Icon for Helper I rankHelper I
2 years ago

OTIF For Multiple Line Orders

I have been trying all day how to calculate OTIF for customer orders when there is multiple lines on the order. If one of the lines is short on a three line order the entire is considered short and n...
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi GMadd 

 

I have understood about your problem, here is the solution I offer:

 

The data from the screenshot you provided is used here

 

First, calculate the total number of orders after removing duplicate values

 

Total_Orders = CALCULATE(COUNTROWS('OTIF Table'), FILTER('OTIF Table', [Delivery] = MAX([Delivery])))

 

 

Then calculate the number of orders when both on-time and full are "yes", and deduplicate:

 

_count1 counts the total number of orders grouped by Delivery

_count2 counts the total number of orders that have passed through the Delivery group and meet the filter conditions

If _count1 and _count2 are equal, it is recorded as a record

 

on time in full = var _count1=COUNTROWS(FILTER(ALLSELECTED('OTIF Table'),[Delivery]=MAX('OTIF Table'[Delivery])))
var _count2=COUNTROWS(FILTER(ALLSELECTED('OTIF Table'),[On-Time]="Yes"&&[Full]="Yes"&&[Delivery]=MAX('OTIF Table'[Delivery])))
return IF(_count1=_count2,1,0)

 

 

Count how many order records there are

 

Total on time in full = CALCULATE(DISTINCTCOUNT('OTIF Table'[Delivery]),FILTER('OTIF Table',[on time in full]=1))

 

 

The results of these measures are as follows, which you can understand in conjunction with DAX

 

Finally, calculate the percentage

 

OTIF % = DIVIDE([Total on time in full], [Total_Orders])

 

 

Here is the result

 

Best Regards,

Nono Chen

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