Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Powerline56
Helper I
Helper I

Measure Help!

 

 

1 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))

vjingzhanmsft_0-1718176417275.png

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

View solution in original post

8 REPLIES 8
rajendraongole1
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





 

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





k

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

Hi @Powerline56 

 

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

vjingzhanmsft_0-1718092334113.png

 

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

j

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

vjingzhanmsft_0-1718176417275.png

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.