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

Occasional Contributor

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

 

4 Replies

Hi Michael

 

DAX is the way to go if you have PowerPivot

 

 

Pivot Averages using DAX.PNG

 

 

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

 

 

 

 

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

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...?]

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

We support Ukraine and condemn war. Push Russian government to act against war. Be brave, vocal and show your support to Ukraine. Follow the latest news HERE