I need assistance trying to figure out how to accomplish something.
I have two fields in my data (Billed Hours and Tech Hrs Available).
I am making a calculated field using those fields to come up with what we call "Proficiency" (Billed Hours / Tech Hrs Available).
We have Regions and then Branches within each Region.
I am bringing this data into a Pivot Table (putting the Avg of "Proficiency" as the VALUE) and my first level is Region and then Branch under Regions.
The calculation works fine at the Branch level, but when you look at the result in the Region level, the resulting value is incorrect b/c it is taking the Average of all the Branch "Proficiency" values instead of performing the calculation with the aggregated Billed Hours / aggregated Tech Hrs Availalble for the specific Region(s).
Is there a way to make this happen in the Pivot Table either using some kind of DAX formula or some kind of setting somewhere?
Any help is greatly appreciated!
see screenshots:
http://screencast.com/t/yOpwpvcKEvlE
http://screencast.com/t/cm7R3QOpG
