How to get an additional total series into a pivot chart?
Posted on 2014-03-16
Hello - I have a worksheet of employee awards, where each row contains the employee name, award type, etc. and the division the employee works in (lets say for example: north, south, east & west).
I have employee counts by division off in another sheet, that I bring in through lookups, and calculate the percent of each reward on a separate column in the data as 1 divided by the number of employees in the given division.
I then sum those percentages in a pivot table, and display them in a pivot chart (bar chart) to show the percentages of awards by region.
Now the user says she wants to see a 5th bar that represents the percent across the company.
Now I have total employee count for the company, also; I'm calculating the percent of each reward in an additional column in the data as 1 divided by the number of employees in the company, just like a did for the regions.
How can I bring this into the pivot chart, so appears as a 5th column for the total company? It's not really a "total" of the 4 regions; it's the total *average* of the four regions.