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):
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.