Log In
Register
Home
July 18, 2020
Offline
Hello,
I am using the calculated item formula to work out an easy percentage. B/(A-T). The formula is working perfectly for a subset of my data (department on it's own or business unit on it's own). The same formula does not work on 2 levels of data in my pivot table. I would like to group the department with all it's business units together and review the utilisation %. I get a DIV/0! error if I attempt to put those two categories together.
Can you help?
Thank you
Norine
Trusted Members
October 18, 2018
Offline
Sample data would be very helpful to providing you with a solution. Updates some sample data and a mocked up solution so that we know exactly what you requirements are.
July 18, 2020
Offline
Apologies, I thought I did. Trying again.
July 18, 2020
Offline
Checking if there might be help for my query?
Moderators
January 31, 2022
Offline
Can't download your file. Tried is several times and in different ways but always get "Couldn't download - something went wrong".
July 18, 2020
Offline
Thank you for checking. I have copied/pasted the data into a fresh workbook. I hope that this help resolve the problem.
I appreciate your assistance.
Norine
Moderators
January 31, 2022
Offline
Looking at your file, it seems that Department 1, for instance, only has data for three Business Units (BU).
Calculated items are applied to all Department/BU option. Including the ones without data. These will thus return the #DIV/0! error. I can't think of a way to eliminate these rows from a regular pivot table.
However, with Power Pivot (the Data Model) you can define a measure that calculates the Utilization % that will only come up if the Department/BU has data. I've added your data to the Data Model, after some necessary transformations with Power Query. It's all in the attached workbook. come back here in case you get stuck.
1 Guest(s)
