Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating Fill Rate - With Exclusions

Hi all

Trying to wrap my head around trying to make this work. I'm trying to calculate two separate fillrates - Absolute Fill Rate (Total Units Shipped / Total Units Ordered), and Qualified Fill Rate. Qualified Fill Rate would be calculated the same, but excluding ifitem has been ordered more than once in the same week that was shipped 0 units (effectively, after the first Qty Shipped = 0 the rest would be excluded for the rest of the week). For example:

 

Item NumberCustomer NumberDateWeekNumQty OrderedQty Shipped
101990012020-01-0111010
101990012020-01-0211010
101990012020-01-031100
101990012020-01-041100

 

For this example, my Absolute Fill Rate for WeekNum=1 be calulated by:

Qty Shipped / Qty Ordered = (10+10+0+0) / (10+10+10+10) = 20/40 = 50%.

 

For this example, my Qualified Fill Rate for WeekNum=1 be calculatd by:

Qty Shipped / Qty Ordered = (10+10+0) / (10+10+10) = 20/30 = 67%

 

Any thoughts how I might accomplish this?

 

Thanks

 

Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Here are two expressions that get your expected results from your example data

     

    Filled =
DIVIDE ( SUM ( Orders[Qty Shipped] ), SUM ( Orders[Qty Ordered] ) )

Qualified =
VAR __filled =
    SUM ( Orders[Qty Shipped] )
VAR __ordersnonzero =
    CALCULATE ( SUM ( Orders[Qty Ordered] ), Orders[Qty Shipped] <> 0 )
VAR __orderszero =
    SUMX (
        VALUES ( Orders[WeekNum] ),
        VAR mindate =
            CALCULATE ( MIN ( Orders[Date] ), Orders[Qty Shipped] = 0 )
        RETURN
            CALCULATE (
                SUM ( Orders[Qty Ordered] ),
                Orders[Qty Shipped] = 0,
                Orders[Date] = mindate
            )
    )
RETURN
    DIVIDE ( __filled, __ordersnonzero + __orderszero )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    Here are two expressions that get your expected results from your example data

     

    Filled =
DIVIDE ( SUM ( Orders[Qty Shipped] ), SUM ( Orders[Qty Ordered] ) )

Qualified =
VAR __filled =
    SUM ( Orders[Qty Shipped] )
VAR __ordersnonzero =
    CALCULATE ( SUM ( Orders[Qty Ordered] ), Orders[Qty Shipped] <> 0 )
VAR __orderszero =
    SUMX (
        VALUES ( Orders[WeekNum] ),
        VAR mindate =
            CALCULATE ( MIN ( Orders[Date] ), Orders[Qty Shipped] = 0 )
        RETURN
            CALCULATE (
                SUM ( Orders[Qty Ordered] ),
                Orders[Qty Shipped] = 0,
                Orders[Date] = mindate
            )
    )
RETURN
    DIVIDE ( __filled, __ordersnonzero + __orderszero )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat