Need help calculating % of on time shipments by month

[kaylastarr]

Hello Helpful People,

I have the following dataset

Supplier Name	Material Number	Stat. Delv Date	G/R Date	Performance
LEONI FIBER OPTICS INC	222-317500	8/5/2022	8/26/2022	Late
LEONI FIBER OPTICS INC	030-001000	8/5/2022	9/19/2022	Late
LEONI FIBER OPTICS INC	030-001100	8/8/2022	8/1/2022	Early
LEONI FIBER OPTICS INC	460-102800	8/12/2022	1/1/1901	Late
LEONI FIBER OPTICS INC	460-102400	8/15/2022	10/21/2022	Late
LEONI FIBER OPTICS INC	460-102400	8/24/2022	1/1/1901	Late
LEONI FIBER OPTICS INC	030-001000	9/6/2022	9/26/2022	Late
LEONI FIBER OPTICS INC	030-000900	9/6/2022	9/19/2022	Late
LEONI FIBER OPTICS INC	222-317900	9/9/2022	10/12/2022	Late
LEONI FIBER OPTICS INC	460-102800	9/22/2022	1/1/1901	Late
LEONI FIBER OPTICS INC	460-113801	10/3/2022	1/1/1901	Late
LEONI FIBER OPTICS INC	030-000900	10/5/2022	10/12/2022	Late
LEONI FIBER OPTICS INC	030-001000	10/5/2022	10/3/2022	Early
LEONI FIBER OPTICS INC	030-001100	10/14/2022	10/12/2022	Early
LEONI FIBER OPTICS INC	222-272500	10/17/2022	10/24/2022	Late
LEONI FIBER OPTICS INC	222-317500	10/20/2022	1/1/1901	Late
LEONI FIBER OPTICS INC	222-317900	10/21/2022	10/18/2022	Early
LEONI FIBER OPTICS INC	030-000800	10/24/2022	10/24/2022	Early
LEONI FIBER OPTICS INC	222-272100	10/26/2022	10/24/2022	Early
LEONI FIBER OPTICS INC	222-272100	10/26/2022	10/24/2022	Early
LEONI FIBER OPTICS INC	222-272200	10/26/2022	1/1/1901	Late
LEONI FIBER OPTICS INC	222-272200	10/26/2022	1/1/1901	Late
LEONI FIBER OPTICS INC	460-102400	10/27/2022	1/1/1901	Late
LEONI FIBER OPTICS INC	460-102400	10/31/2022	1/1/1901	Late

I am trying to create a line plot that shows the percentage of materials that are shipped "Early" each month. I am using the below measure to calculate the %:

On Time Shipments = CALCULATE(COUNTROWS(Suppliers),OR(Suppliers[Performance]="On Time",Suppliers[Performance]="Early")=True)/CALCULATE(COUNTROWS(Suppliers),Suppliers[Performance]<>"Not Delivered Yet")

This graphs correctly EXCEPT when ALL of the lines are late. Then it has no data, I would like it to have 0 if all the materials are late. 

Any help is appreciated. 

Here is the graph that results (as you can see September has no data ploted due to everything being late)

[v-cgao-msft · Accepted solution]

Hi @kaylastarr ,

Try adding zeros to the numerator, like this:

On Time Shipments = 
VAR _ON_TIME = 
CALCULATE (
    COUNTROWS ( Suppliers ),
    OR ( Suppliers[Performance] = "On Time", Suppliers[Performance] = "Early" ) = TRUE
) + 0
VAR _DELIVERED =
    CALCULATE (
        COUNTROWS ( Suppliers ),
        Suppliers[Performance] <> "Not Delivered Yet"
    )
VAR _RESULT = DIVIDE(_ON_TIME,_DELIVERED)
RETURN
_RESULT

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[kaylastarr]

This worked! without pulling in unnecessary 0's thank you so much!

[djurecicK2]

Hi @kaylastarr ,

You can add an IF statement to the measure.

Something like this:

On Time Shipments =
VAR CountofNonLateShipments =
    CALCULATE (
        COUNTROWS ( Suppliers ),
        OR ( Suppliers[Performance] = "On Time", Suppliers[Performance] = "Early" ) = TRUE
    )
RETURN
    IF (
        ISBLANK ( CountofNonLateShipments ),
        0,
        CALCULATE (
            COUNTROWS ( Suppliers ),
            OR ( Suppliers[Performance] = "On Time", Suppliers[Performance] = "Early" ) = TRUE
        )
            / CALCULATE (
                COUNTROWS ( Suppliers ),
                Suppliers[Performance] <> "Not Delivered Yet"
            )
    )

Please mark as accepted solution if this has resolved the issue.

[kaylastarr]

I have tried adding the IF statement before and it ends up pulling in ANY material that is on the list as 0 instead of just the ones for that month. But the solution by Gao above works.