Matrix with hierachy does not show correct Totals on higher agregation Dealing with Hourrates

[BobKoenen]

Hi all,

I am creating an overview based on hourly rate and nr. of hours for employees in an Matrix using a hierarchy. In which the Department is the highest level en employee the lowest. However I cannot figure out how to deal with the HourRates. 

The data looks like this:

Department	Person	Hourrate	Hours	Total
A	John	10	20	200
B	Jane	10	30	300
B	Joe	15	20	300
B	James	50	1	50
Total		85	71	850

When I fold the Matrix the calculations become incorrect at the department level. 

A		10	233	200
B		75	51	3825
total		85	284	4025

This is because the Hourrate colomn is summed. I tried to switch off the summing in the values plane of the visual but that does not work for me because I need the totals on departmentlevel.  Using Average also is not correct because of the differences in amount of hours. 

I think i Need a formula which adds all the values on the employee level en shows that as in the total row. Not calculating the amount from the total row itself. 

I hope you can help me. 

[Anonymous · Accepted solution]

The hour rate on the department level, or any level for that matter, needs to be a weighted average of the hour rates for each employee. The weights are the hours.

Why so? Because [Hourrate] * [Hours] = [Total] and this equation must be maintained on each row, even the last one.

So, the answer is easy: the hour rate must be the sum of rate * hours over all visible employees and divided by the sum of the hours for the same set of employees.

Best
D

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also, you may need this:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

[BobKoenen]

Thankx this is very Helpfull