Answered by:
Grand total not showing desired number
Question

I am calculating the expected number of hours that a department can deliver. To do that I need to multiply the each employees availability in the year (meaning how many months they have been employed) with the expected number of hours per month (160,33 hours).
I have calculated the number of months a person has been employed by the following:
Number of months=Calculate(
DISTINCTCOUNT(Time[Month_name]);
'Hours and Absence')
Where 'Hours and Absence' is my fact table with all employees and which months they have been employed.I then multiply this figure with 160,33 doing like:
Expected hours = 160,33 * 'Hours and Absence'[Number of months].
This gives me the correct value when I have employees in the rows as I then get for instance this (filtered for 2015):
Employee Number of months Expected hours
Jack 12 1.924
Bill 6 962
My problem comes when looking at the "Grand Total" as it returns:
Employee Number of months Expected hours
Jack 12 1.924
Bill 6 962
Grand total 12 1.924
I then added the following to my calculation in order to take the number of employees into account:
Number of employees:
=Calculate(
DISTINCTCOUNT(Employees[Initials]);
'Hours and Absence')
Which returns the number of employees in the department. I then modify my calculation with the following:
Expected hours:
=(160,33 * 'Hours and Absence'[Number of months])*'Hours and Absence'[Number of Employees]
This then results in the following:
Employee Number of months Number of employees Expected hours
Jack 12 1 1.924
Bill 6 1 962
Grand total 12 2 3.848
Which is still not correct  I need the Grand Total for Expected Hours to be 1.924+962= 2.886.
How can I achieve that?
Best regards
Morten
Monday, February 8, 2016 2:43 PM
Answers

Hi,
For this you need to compute the Expected hours for each employee and then SUM it up. In your approach , it is getting the number of month as 12 and 2 at the grand total level which gives 12*160.33 + 12*160.33 . But the calculation should be like 12*160.33 + 6 *160.33. Try something as below formula which calculates the ExpectedHours for each employee and then sums it up.
Note : Consider the below as pseudocode. Not tested.
SUMX ( SUMMARIZE( 'Employees' ,Employees[Initials] ,"ExpectedHours" ,160,33 * 'Hours and Absence'[Number of months] ) ,[ExpectedHours] ) )
Best Regards Sorna
 Marked as answer by Morten_DK Tuesday, February 9, 2016 3:09 PM
Monday, February 8, 2016 5:06 PM
All replies

Hi,
For this you need to compute the Expected hours for each employee and then SUM it up. In your approach , it is getting the number of month as 12 and 2 at the grand total level which gives 12*160.33 + 12*160.33 . But the calculation should be like 12*160.33 + 6 *160.33. Try something as below formula which calculates the ExpectedHours for each employee and then sums it up.
Note : Consider the below as pseudocode. Not tested.
SUMX ( SUMMARIZE( 'Employees' ,Employees[Initials] ,"ExpectedHours" ,160,33 * 'Hours and Absence'[Number of months] ) ,[ExpectedHours] ) )
Best Regards Sorna
 Marked as answer by Morten_DK Tuesday, February 9, 2016 3:09 PM
Monday, February 8, 2016 5:06 PM 
Thanks a lot  it worked without any need for modification.
Best regards
Morten
Tuesday, February 9, 2016 3:09 PM