tbakbradley
Board Regular
- Joined
- Sep 24, 2010
- Messages
- 136
I have a pivot table I've used for years, but I have to add a Column to the right for (% On Time), which means if someone wants to use a Filter, it messes all that up.
Here is what I have for the Pivot Table Fields, and was hoping someone could help me.
COLUMNS- "Late or On Time". This is a column in my data worksheet that calculated if the request was answered Late or On time. So the cells on that column say "LATE" or "ON TIME"
ROWS- I have two Fields on here. 1-"Employee Name" and 2- "System Source", which just shows which system the work originated.
Values- This is a Count of "System Source"
So the results would look like:
Count of Source Column Labels
Row Labels LATE ON TIME Grand Total (Manually Added Column with Equation I want automated)
John Doe 100% SUM(C8:C11)/SUM(D8:D11) where On Time is Column C and Grand Total is Column D
System 1 1 1
System 2 51 51
System 3 2 2
System 4 47 47
Bill Smith 93.10% SUM(C27:C28)/SUM(D27:D28) where On Time is Column C and Grand Total is Column D
System 1 2 28 30
System 2 2 26 28
The Column in Bold above is to the right of my Pivot Table. I have an equation to calculate the Percentage of On Time for each Employee for all the System Sources.
Is there a way in the pivot table I can set this up to show the "% On Time" for each employee comparing the On Time Total of all systems to the Grand Total of all systems?
Any help would be greatly appreciated as we now have a need to do a lot of filtering for several options, which won't work with my manual column of equations.
Here is what I have for the Pivot Table Fields, and was hoping someone could help me.
COLUMNS- "Late or On Time". This is a column in my data worksheet that calculated if the request was answered Late or On time. So the cells on that column say "LATE" or "ON TIME"
ROWS- I have two Fields on here. 1-"Employee Name" and 2- "System Source", which just shows which system the work originated.
Values- This is a Count of "System Source"
So the results would look like:
Count of Source Column Labels
Row Labels LATE ON TIME Grand Total (Manually Added Column with Equation I want automated)
John Doe 100% SUM(C8:C11)/SUM(D8:D11) where On Time is Column C and Grand Total is Column D
System 1 1 1
System 2 51 51
System 3 2 2
System 4 47 47
Bill Smith 93.10% SUM(C27:C28)/SUM(D27:D28) where On Time is Column C and Grand Total is Column D
System 1 2 28 30
System 2 2 26 28
The Column in Bold above is to the right of my Pivot Table. I have an equation to calculate the Percentage of On Time for each Employee for all the System Sources.
Is there a way in the pivot table I can set this up to show the "% On Time" for each employee comparing the On Time Total of all systems to the Grand Total of all systems?
Any help would be greatly appreciated as we now have a need to do a lot of filtering for several options, which won't work with my manual column of equations.