Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Measure Help!

   
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

     

    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!

rajendraongole1's avatar
rajendraongole1
Super User
2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

     

     

    • rajendraongole1's avatar
      rajendraongole1
      Super User
      2 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        k