Need help getting correct average when using calculated field in pivot table with multiple levels

[Michael Waters]

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

[#2]

Hi Michael

DAX is the way to go if you have PowerPivot

You create a measure for Billed Hours and one for Available Hours and then a 3rd for Proficiency %

This will then give you the result your after

[#3]

Thanks, Wyn... I will try your suggestion and let you know how it goes.

[#4]

Wyn,

I am trying to understand how you did that and I think im missing something.

I just clicked in an empty cell at the bottom in the Data Model and was trying to create the Proficiency calculation measure.  The only thing it will let me pick is [Sum of Hrs Billed], etc.  I cannot pick just [Hrs Billed].

I also noticed you have multiple tabs at the bottom.  One for Data, one for Values, and one for Values2.

What are those and how do you make them?

You put your measures in the Values tab instead of the actual Data tab.  There doesnt appear to be anything in the Values2 tab.

Sorry.. im new to DAX... just trying to learn this... 

Thanks for the help.. 

[nevermind.. I see now where you made the Proficiency% measure a calculation of the other two measures, which each had a sum(hrs billed) or sum(hrs available) building them.  I am still curious about the extra tabs at the bottom, however...?]

[#5]

Hi Michael,

Take a look at this article I wrote. It explains why I do a separate table for storing my measures

How to Group Measures in Power Pivot
https://www.linkedin.com/pulse/how-group-measures-power-pivot-wyn-hopkins