DAX formula for working hours from '# Employees', 'working days' and '9 Hours a day'

[JordanJackson]

Hi All, 

I'm looking for a way to calculate the 'working hours' from the data I have. I aim to get the company wide total working hours, and then for the measure to be able to be filtered to the relevent region/site when needed. 

I have columns with:

- Total Employees for a site each month

- Total Working Days that month 

and each day is a 9 hour work day 

I am struggling to develop the correct measure to calculate this. I am looking at something like

'Employees' X ('Working Days' X 9.5)

But as the the specifics and the operation are stumping me. 

Any help would be greatly appreciated, 

Thankyou

[NickA01]

Hope this helps. 

The Nick

If the post helps please give a thumbs up || If it solves your issue, please accept it as the solution to help the other members find it more quickly.

If it's the biggest heap of stinky smelly stuff then I'm sorry .

[JordanJackson]

Amazing thank you! I believe this has resulted in the correct awnser for when displaying in a graph over time. However, is the reason the total is broken due to the issues with powerbi totals?

My total is displaying as 76 which is incorrect as it should be 553.06. 

In your example your total appears to be incorrect also. 

[NickA01]

The first version didn't look correct so I have rewritten 
EG Site A 10 employees in Jan :  Working days in Jan = 21 : Work hrs in Jan =(21*9) =189
Site A Jan work Hrs =WorkHrsInJan * #Employeer      189*10 =1890

The Nick

If the post helps please give a thumbs up || If it solves your issue, please accept it as the solution to help the other members find it more quickly.

If it's the biggest heap of stinky smelly stuff then I'm sorry .

[JordanJackson]

Corrected for your changes, but my total is still not matching the results. Do you know why this would be?

[NickA01]

In original post you state each day is a 9 hour work day 
but you also mention
'Employees' X ('Working Days' X 9.5)
----------------------------------------------------------------
For the top row of your table,  your Hours per day = 9.5  
Staff Month Total = 21
Working Days = 14
Hours per day = 9
(21*14) =294  --> *9 = 2646
(21*14)=294 --> *9.5 = 2793    
------------------------------------------------------------
In this one , 

(122*76)=9272  -->*9 =83448
(122*76)=9272  -->*9.5 = 88084

So it looks like you are multiplyig by 9.5

[JordanJackson]

Sorry, 9.5 is the correct value. 

Whats getting me is the 2793+2299+10687+3562=19,341 
This is what would be the correct total hours worked for all of the entries 

But instead of this figure it gives 88,084. 

[NickA01 · Accepted solution]

Sorry for delay here - busy day.  
You may have already solved this but if not, see if this helps 
- I've adjusted by test data so it's in line with yours. 

The Nick

If the post helps please give a thumbs up || If it solves your issue, please accept it as the solution to help the other members find it more quickly.

If it's the biggest heap of stinky smelly stuff then I'm sorry .

[JordanJackson]

I have found the soloution, of course it is seems simple when you have a greater understanding of the problem. I think I was too caught up in thinking that the process needed to be done by using a measure, when it was calculated columns that I needed to create. Thankyou for your help!

[JordanJackson]

Hi, Sorry about the delayed reply I had to work on some other projects. Thanks for getting back to me. 

All of this information is contained in a single table, so does that mean the lookup measure isn't relevant? Or should it still be included? Also is the key now also not necessary? 

Thank-you again for being so helpful!

[JordanJackson]

This is the current Measure I am using, however with this the issue I am experiancing is that when in a card the value is 88,000. However when placed into a graph over time then when Summing the months together I get a total of 19,340. 

Monthly Working Hours =

VAR WorkingDaysXHours = SUM('KPI proof of concept Test'[Working Days]) * 9.5

RETURN

    SUM('KPI proof of concept Test'[JCE Month Total]) * WorkingDaysXHours