Calculated Item results in empty rows Pivot Table

[Momidashi]

Good day everyone,

I want to show the trend per week in on time delivery (per carrier). I have added a calculated item column to the pivot table to get the required 'on time' percentage. Unfortunately after using a calculated item I am not able to correctly filter the pivot table: Empty rows are not removed when using the slicers/filters, therefor making the graph unusable.

Ideally the graph should display the percentage of ON TIME deliveries and move with the slicers, filtering out empty rows. For example: When selecting Shipped Year 2021 it should not show the weeks of 2020. Currently the rows I do not want to see have a #DIV/0! error, I have tried setting all errors to blank cells but it does not make the pivot table more dynamic. Setting the error to a value such as 0 and filtering the column to not show value 0 also does not do anything.

Desired result (red boxed items should be gone based current filter 2021):
example.jpg


From hours of googling I understand this is a common problem with calculated item, I just don't know how to solve it any other way. Please have a look at the example file included containing all relevant columns and sample data.
Is there a way to get the desired result? I hope I explained clearly.

Thanks in advance.

[JeteMc]

This proposal, modeled on Sheet2, employs Power Pivot.
The following DAX measure calculates the percentage on time:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Momidashi]

This is perfect. I have no experience with Power Pivot / DAX and you gave me the tools to look in the right direction, following a course now to get a better understanding.

Thank you very much JeteMc!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

[Momidashi]

Hi JeteMC and other readers,

I forgot one crucial step, in addition to ON TIME and LATE values there are also PROCESSING values which should be excluded from the calculation.
I have changed the last 50 columns to PROCESSING. Could you please provide the formula which will exclude these?

Thanks in advance!

[JeteMc]

Try editing the measure to read:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Momidashi]

Thanks a lot JeteMc! Closing the thread.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.