Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |