I'm looking for a solution to calculate the % of an item in another field in a pivot table.
This is the pivot table using sample data before adding the % of to the value calculations.
Screenshot 2020-06-24 at 15.50.07.png
Available = an item in a field called "Data Type" which shows the available time for staff in a team each month.
Booked = another item in the field called "Data Type" which shows scheduled time for staff in a team each month.
This is then broken down using the field "Job Type" which includes three items (in-house, quoted, and confirmed)
I'm looking to report on team capacity so I need to calculate the the percentage of the item "Available" that is booked by each item in "Job Type". This cannot just be the percentage of the total of Job Type because the booked time may be more or less than the available time.
I have set the value field to % of Available and this is the result:
Screenshot 2020-06-24 at 16.01.27.png
The available column is 100% as expected as the other values will be a % of this. However, I'm just getting n/a in the values for Job Type where I would expect to see the % of available. I'm assuming the pivot table doesn't like calculating against an item in another filed rather than its own parent.
Is there a way to resolve this or an alternative way to calculate?
I'm keen to keep the calculation within the pivot table because my aim is to use pivot charts with slicers to create a dashboard and the key to that is getting this % of calculation to work, or another method that gives the same result.
Thanks
Bookmarks