# 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

• 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 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 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