Forum Discussion

Michael Waters's avatar
Michael Waters
Copper Contributor
Jan 02, 2017

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

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 ...
BI & Data Analytics
Need Help
Wyn Hopkins's avatar
Wyn Hopkins
MVP
Jan 03, 2017

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

 

 

 

 

Michael Waters's avatar
Michael Waters
Copper Contributor
Jan 07, 2017

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

  • Michael Waters's avatar
    Michael Waters
    Copper Contributor
    Jan 08, 2017

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

Resources