Forum Discussion
Calculating Average Hours Worked/Week by Week, Month, Year
Hey everyone, I believe this should be a fairly straight forward question, so let's give it a shot! I have a very simple database that tracks daily employee hours worked (see below for a small sample of how it is laid out). I also have a calendar table. I am looking to calculate the Average Hours Worked per each employee, across the company. The calculation should be simple. I would Sum Hours Worked, Divided by Count of Employees, Divide by Count of Total Weeks. The problem is, I'm not sure how to account for partial weeks? For example, if I wanted to show our company's Average Hours Worked when I filter my report to January 2019 the count of 'Total Weeks' becomes 5 as there is technically a partial week in the month. This understates our Average Hours Worked. It would be easy if I knew that the user was going to consistenly be looking at the report by Month, or Quarter, or Yearly, then I could just hard code 4, 12, & 52 respectively, but I need this to be dynamic.
8 Replies
Hi Anonymous
when you select "january", the number of total weeks is 5, but you need the "Average Hours Worked/Week" =
Sum Hours Worked(1/1~1/31), Divided by Count of Employees, Divide by Count of Total Weeks(4),
Right?
Or the number of weeks for "january" =4+4/7
Best Regards
Maggie
Hi Maggie,
v-juanli-msft wrote:when you select "january", the number of total weeks is 5, but you need the "Average Hours Worked/Week" =
Sum Hours Worked(1/1~1/31), Divided by Count of Employees, Divide by Count of Total Weeks(4),
Right?
Yes, that is correct. I know this is not strictly a PowerBI question, and more of a general analytical one, but I am just having difficulty with understanding how to do this kind of calculation.
Thank you,
Mike
Hi Anonymous
Create a date table connected to main data table,
date = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]),"weeknum",WEEKNUM([Date],2),"weekday",WEEKDAY([Date],2))
Then create measures in main data table
total week = IF(WEEKDAY(LASTDATE('date'[Date]),2)<>7,COUNTX(VALUES('date'[weeknum]),'date'[weeknum])-1,COUNTX(VALUES('date'[weeknum]),'date'[weeknum]))average = SUM(Sheet3[hours])/DISTINCTCOUNT(Sheet3[employee])/[total week]
(assume start of a week is Monday, end is Sunday)
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thank you, this helped so much! The total week formula works perfectly when I filter by month or quarter from my calendar table, however it doesn't seem to filter by year? For example, when I select 2019 I'd like it to total the number of full weeks completed YTD. Selecting 2018 would display 52 weeks; the formula currently displays 51 weeks for some reason? Selecting 2019 would show the count up to the date my data is through.
Also, when I select "Jan, Feb, Mar" of 2019, shouldn't the result come out to 12?
Thank you,
