Calculating OTIF at a line level can be useful but can also obfuscate serious deficiencies. Customers likely do not care about individual lines of orders but rather about whether their complete orders are being fulfilled on time and in full. To calculate OTIF at the order level, do the following:
- Create the following measure:
On Time In Full =
VAR __Ordered =
SUMMARIZE(
'R05_Table',
'R05_Table'[Order],
"__Ordered",
SUM('R05_Table'[Ordered])
)
VAR __OTIF =
SUMMARIZE(
FILTER(
'R05_Table',
'R05_Table'[Ordered] = 'R05_Table'[Shipped] &&
'R05_Table'[DeliveredDate] <= 'R05_Table'[CustomerDueDate]
),
[Order],
"__OTIF",
SUM('R05_Table'[Shipped])
)
VAR __Table =
ADDCOLUMNS(
NATURALLEFTOUTERJOIN(__Ordered,__OTIF),
"__Percent",
IF(ISBLANK([__OTIF]),0,[__OTIF] / [__Ordered])
)
RETURN
DIVIDE(
COUNTROWS(FILTER(__Table,[__OTIF] = 1)),
COUNTROWS(__Table),
0
)
- Add the On Time In Full measure to the Values area of the line chart and table charts we created previously.
Here, the calculation for the On Time In Full measure is more complex. We start by creating a table variable, __Orders, that uses SUMMARIZE to group the R05_Table table by the Order column. We use ADDCOLUMNS to add the __Ordered column. The __Ordered column sums the Ordered column. Thus, the __Orders table now contains a row for each order with the total amount of products requested for the entire order.
Now, we create the table variable, __OTIF. For this table, we filter the R05_Table table to only those lines that meet the OTIF criteria. Specifically, we return only the rows in the R05_Table table that have an Ordered value that equals the Shipped value and have a DeliveredDate that is less than or equal to the CustomerDueDate. Again, we use SUMMARIZE to group this filtered table by the Order column. We use ADDCOLUMNS to add the __Shipped column. The __Shipped column sums the Shipped column. Thus, the __OTIF table now contains a row for each order with the total amount of products shipped on time for the entire order.
Next, we create a third table variable called __Table. We use the NATURALLEFTOUTERJOIN function as the basis for our table. NATURALLEFTOUTERJOIN uses natural left outer join semantics to merge the __Orders and __OTIF tables. This means that all of the rows from the left table (__Orders) are included and match the rows from the right table (__OTIF). Then, we use ADDCOLUMNS to add the __Percent column, which is simply a division of the __OTIF and __Ordered columns in our new table. However, since there may be no matching records in the right table, __OTIF may be blank. Thus, we check for this and, if __OTIF is blank (ISBLANK), we assign a value of zero.
Now, we can return a value for On Time In Full by simply dividing the rows in __Table where the __Percent column is 1 by the total number of rows in __Table.