cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

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

1 ACCEPTED SOLUTION
Resolver II

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 .

10 REPLIES 10
Resolver II

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 .

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.

Resolver II

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 .

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

Resolver II

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

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.

Resolver II

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 .

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!

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!

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors