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!

8 Replies

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