Pivot Tables - Averaging group and grand totals

[jlyonofaz]

I have a pivot table that is fed from over 10,000 lines of data of a single worksheet. Data is structured into 14-plus geographic groups (East, Central, West, etc). Within each group there are employees, assigned to Projects assigned to Workgroups. Within my worksheet, each row represents a separate Project and assigned employee. Hours applied to each project are recorded. I have a calculated field in the worksheet that indicates the percentage of time (utilization) applied to that project with respect to the standard labor-hours in the reporting period (e.g. project time = 40 hours, period hours = 160, utilization = .25). Each employee will have multiple projects recorded. I’ve included the Sum of Utilization in the PT grouped by Geography & Workgroup. QUESTION: How do I get the Geography group totals and Report Totals for the Sum of Utilization to actually be an Average of the Utilization for each Geographic Region and Overall Average for the report rather than a total of all utilizations within that region/report?

 

[jlyonofaz]

After multiple attempts at devising a solution to my Pivot Table percentage summary totaling issue, I came across a sample table on another site (http://www.savefile.com/files/1681330) that I was able to adapt to my needs. After adding the counter column to my data source, I recreated my pivot table with the Sum of Counter now included. Using GetPivotData, I was able to create a formula to divide the Group Sum of Utilization by the Group Sum of Counter for each of the groups.

I’ll admit, I still don’t fully understand some of the formulas in the file (see link above). I did have to use the Name Manager to define some of the labels in order to get the Counter formula to function. After everything, the pivot table and GetPivotData functions are performing as I need. Also, just for cosmetics, I changed the font colors on the Sum of Counter column such that only the Group Total entry is visible. For the Sum of Utilization (the percentage total causing the issue), I changed the font color on the Totals such that they were not visible.