topic Re: OTIF For Multiple Line Orders in DAX Commands and Tips

OTIF For Multiple Line Orders

GMadd — Tue, 19 Dec 2023 23:18:06 GMT

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 not on time. I know how to calculate the on-time part by having a formlua to compare the DlvPlnd PGI dt (date to ship) and the Act. Gds Mvmnt Date (date shipped). Also, know how to calculate if it was shipped in full. I am stuck on how to calculate the OTIF % based for each Delivery considering if one of the lines was short. Keep in mind everything is based off of column "G" Delivery meaning every item on the specifed delivery always shipped together. I know the data in the screen shot is in excel but I am wanting to write a DAX measure to handle this in Power BI. Thank you for your help.

Re: OTIF For Multiple Line Orders

Anonymous — Wed, 20 Dec 2023 09:14:18 GMT

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.

Re: OTIF For Multiple Line Orders

GMadd — Thu, 21 Dec 2023 19:39:49 GMT

Few questions

I could not get your measure for counting deliveries to work:

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

I used

Total_Orders = CALCULATE(DISTINCTCOUNT(OTIF[Delivery])) and it gave me the desired result.

Second question

Not sure I understand what the value should be. In my full report it produced a value of "1"

is this the desired result?

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)

Thank you for responing so quickly.