Pivot table arithmetic

[DaverD]

Hello.
I am having difficulty with and looking to have an Excel pivot table perform arithmetic. Any insight would be appreciative.

The use case is with resource management. My goal is to have Excel tell me utilization of a person's time given maximums and plans against the maximums.

The maximums are defined in a reference table that identifies people, their role, and the maximum number of hours per week each are planned across five different work categories.

In addition, there is a staffing plan for these people that lists:
The person
The project
The project's work category

A column for each week and the number of hours that person will work that project that week.

This matrix of work over time is transformed into a normalized table via Power Query. I create a pivot table from the normalized table so rollups can be created.



Getting a rollup by person for all the work assigned to them is not an issue. However, it is the utilization I am having difficulty calculating. If I define utilization as "Hours from the staffing plan" divided by the "Maximum hours for a work category", I get the right percentages for the individual record (i.e. non-rollup record). However, rollups produce undesired percentages because the utilization shown is an average of individual records as opposed to doing the "Hours from the staffing plan" divided by the "Maximum hours for a work category".

Does anyone know either (1) how to get Excel to not take an average for rollups or (2) another approach I can take to determine utilization?

I've attached an example. Allow me to elaborate on it.

It is made up of three tabs. Tab 1 is for data entry. Columns A through E uniquely identify a person assigned to a project. A person's name is in Column E. The project which I call 'Work Entity' is in Column D. Work entities are categorized by values in Column C. The person's role is in Column B. Finally, Column A (Domain) represents the maximum number of hours allocated for that Role for that Work Category. For example, in looking at Row 13, 14.80 hours is the maximum number of Large Project hours planned for Network Admins with Person A being a Network Admin.
Columns F through the end represent time in weeks with the matrix populated with planned hours for a person to a project for that week.

Tab 2 is a data table from Tab 1 created with Power Query. The purpose of this table is to be source for pivots.

Tab 3 is a pivot of Tab 2 and where my issues arise. My goal is to identify under and over allocated people. If I know the maximum number of hours allocated for each person-work category (Tab 1; Column A) and the pivot can sum up the hours for each person-work category, then I should be able to determine over allocation (planned/allocated > 100%) or under allocated (planned/allocated < 100%).

I've created a calculated field called Allocation and has the formula of "=IF(Domain>0,Hours/Domain)". I use the calculated column in the Tab 3 pivot.

I want the value 'Sum of Allocation' column to be equal to Hours/Domain for all rows. All is fine when one looks at an individual record. All is not fine when rollups happen.

Rather than getting Hours/Domain for the row, I am getting the average of the 'Sum of Allocation' for rollup section. For example, on Tab 3, look at Columns G, H, I for Rows 20-28 (Person F's Large Projects). For each individual project row, Column I is what I want. However, the rollup in Row 21 is not what I want.

How do I get Row 21/Column H to be equal to Row 21's Column G divided by Row 21`s Column H?

-DaverD

[Pecoflyer]

to be honest with you there are not many members who will go through such a long explanation although you had the good idea of posting a file.
The best way of being helped is to ask one question at a time described as shortly as possible. When it's solved, start a new thread ( eventually linking to the first one), and so on.