Measure Help!

[rajendraongole1]

Hi @Powerline56 - Can you use the below measure to get calcuate the "OTIF Without Dupes" correctly

This measure uses SUMMARIZE to create a temporary table that summarizes by Location ID and Sales Order, ensuring that the distinct count and filtering logic are correctly applied.

OTIF Without Dupes =
VAR OrdersTable =
SUMMARIZE(
'SalesTable',
'SalesTable'[Location ID],
'SalesTable'[Sales Order],
"OnTime", MAX('SalesTable'[OnTimeFlag])
)

VAR TotalOrders = COUNTROWS(OrdersTable)

VAR OnTimeOrders =
COUNTROWS(
FILTER(OrdersTable, [OnTime] = 1)
)

RETURN
DIVIDE(OnTimeOrders, TotalOrders, 0)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[rajendraongole1]

Hi @Powerline56 - Modified to remove the filters in above measure can you try this?

OTIF Without Dupes V4 =
VAR OrdersTable =
SUMMARIZE(
'OTIF Showcase Sales order rollup',
'OTIF Showcase Sales order rollup'[Location ID_Desc],
'OTIF Showcase Sales order rollup'[sales_order_number],
"OnTime", MAX('OTIF Showcase Sales order rollup'[On time])
)

VAR TotalOrders =
CALCULATE(
COUNTROWS(OrdersTable),
REMOVEFILTERS('OTIF Showcase Sales order rollup'[sales_order_number])
)

VAR OnTimeOrders =
CALCULATE(
COUNTROWS(
FILTER(OrdersTable, [OnTime] = 1)
),
REMOVEFILTERS('OTIF Showcase Sales order rollup'[sales_order_number])
)

RETURN
DIVIDE(OnTimeOrders, TotalOrders, 0)

I hope it works, 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Powerline56]

k

[Powerline56]

@rajendraongole1 , any advice on getting the above requirement added in the measure?

[v-jingzhan-msft]

Hi @Powerline56 

Please check if this can meet your requirement. You can download the pbix to see details. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Powerline56]

j

[v-jingzhan-msft · Accepted solution]

Hi @Powerline56 

Sure, please try this measure

Measure 1 = 
var _ZeroQty = COUNTROWS(CALCULATETABLE(VALUES('Table'[sales_order_number]),'Table'[OTIF]=0))
var _total = DISTINCTCOUNT('Table'[sales_order_number])
return
IF(ISINSCOPE('Table'[sales_order_number]),IF(_ZeroQty=0,1,0),1-DIVIDE(_ZeroQty,_total))

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!