Log In
Register
Home
Active Member
April 12, 2023
Offline
I am trying to add a calculated field to my pivot table that finds the percent of a total number of jobs to the total number of jobs that had a specific type of appointment. The current % field is manually calculated, I keep getting error while trying to add calculated field. Total jobs field is a count of the total number of jobs, and the request PII field is a TRUE/FALSE formula that I have returning a 1 or 0 and then summing those.
Moderators
January 31, 2022
Offline
The calculated field you are trying to create divides a number (PII's) by a text (Job) resulting in a #DIV0! error for all rows. If you want to stay with a regular pivot table you could enter an extra data column with all ones and call it, for instance, JobN. Then drag Job in the Row field and both JobN and PII in the Value field (to sum). Then the calculated field (=PII/JobN) will work as you want it to. The attached file contains an example.
But, if you want a more sturdy solution you would have to look into Power Pivot (PP) where you can create DAX measures that count the number of Jobs and PII requests and the related percentages. I haven't gone that far now as PP is not supported in Excel for the Mac. And I happen to be working a Mac right now.
Edit: Added another file that also holds the suggested PP solution.
1 Guest(s)
