I need to merge data shown in the two charts below. Top right is a stacked bar based on the "Available Hours" table for Full and Part-time workers. Bottom right is a stacked bar based on the pivot of "Estimated hours" to show estimates of a team's project time. The dashed arrow indicates how I need a side-by-side comparison of each team's Estimated vs Available capacity vs Returned value.
I've tried adding the individual fields from "Available Hours" to the pivot in various ways but the values are duplicated across projects or teams and so totaled incorrectly. Merging the table queries in the data model produces the nested tables as shown below
If I expand the data to chart it as the data is then duplicated (besides not being stacked) :
How is it possible to either create an intermediate data table, or create a dynamic chart that aggregates the data from the pivot and the base?
Manually manipulating the data isn't really an option as the data is obtained via a connection, is a much larger data set and changes regularly. I'm OK with VBA if that is needed?
Source Data:
Estimated hours for a team to fulfil a project
Region Team Project Project Hours ReturnedValue
North teamA Project1 54 100
North teamA Project2 53 132
East teamB Project2 34 85
East teamB Project7 19 54
Team FT Hours PT Hours Total Hours
teamA 100 50 150
teamB 97 35 132
Sample File: Sample file