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 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.

 

2 Replies

  • 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.

     

     

     

     

     

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

    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.