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
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.
Apologies, I thought I did. Trying again.
Checking if there might be help for my query?
Can't download your file. Tried is several times and in different ways but always get "Couldn't download - something went wrong".
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
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.
