Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello there!
I'm basically trying to calculate a KPI called OTIF (on time in full delivery) and OTIF%
Easiest to explain through a table:
Order number | Part number | Difference in delivery date (days) | OTIF |
1000 | ABC | -2 | 0 |
1000 | CDE | 4 | 0 |
1000 | EFG | -2 | 0 |
1001 | ABC | -1 | 1 |
1001 | CDE | -1 | 1 |
1002 | ABC | 0 | 1 |
1002 | CDE | -1 | 1 |
So basically, if the order number is the same --> get the max of difference in delivery days --> if max is less than or equal to 0, OTIF = 1 but if it is greater than 0 = 0.
Then I want to calculate the percentage for all those lines:
2 orders was OTIF and 1 was not --> 2/3 OTIF. How do I only calculate the OTIF = 1 once per order number?
Thanks for any help. This has bothered me for a couple of days.
Solved! Go to Solution.
Hi @Anonymous
Try the below calculation it can be used as a Column or Measure.
Column =
CALCULATE(
VAR __max = MAX( 'Table'[Difference in delivery date (days)] )
RETURN INT( __max <= 0 ),
ALLEXCEPT( 'Table', 'Table'[Order number] )
)
Hi @Anonymous
Try this
Measure =
DIVIDE(
CALCULATE(
DISTINCTCOUNT( 'Table'[Order number] ),
ALL( 'Table' ),
'Table'[Column] = 1
),
COUNTROWS( ALL( 'Table'[Order number] ) )
)
Hi @Anonymous
Try the below calculation it can be used as a Column or Measure.
Column =
CALCULATE(
VAR __max = MAX( 'Table'[Difference in delivery date (days)] )
RETURN INT( __max <= 0 ),
ALLEXCEPT( 'Table', 'Table'[Order number] )
)
Thanks alot! That was fast!
It worked at least as a calculated column to return 0 and 1.
Do you also happen to know how I could calculate the %?
I guess that would be a measure in a similar way, using allexcept also?
OTIF% = count distinct order number where OTIF = 1 / all orders
Hi @Anonymous
Try this
Measure =
DIVIDE(
CALCULATE(
DISTINCTCOUNT( 'Table'[Order number] ),
ALL( 'Table' ),
'Table'[Column] = 1
),
COUNTROWS( ALL( 'Table'[Order number] ) )
)
You're a genius!
Thanks alot Mariusz
Hi @Anonymous
Glad I could help.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
22 | |
7 | |
6 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |