Calculating Fill Rate - With Exclusions

[Anonymous]

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 Number	Customer Number	Date	WeekNum	Qty Ordered	Qty Shipped
101	99001	2020-01-01	1	10	10
101	99001	2020-01-02	1	10	10
101	99001	2020-01-03	1	10	0
101	99001	2020-01-04	1	10	0

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

[mahoneypat · Accepted solution]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[mahoneypat · Accepted solution]

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