Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
HenrikH29
Helper I
Helper I

Problem calculation total work hours

I need to calculate the total work time per employee for a given period (ie month).

 

Our employees have a normal work time calender with a daily work time ie 7.5 hours monday to thursday and 7 hours friday totalling 37 hours per week. Some employees work parttime and have a differenct worktime calender ie. 6 hours a day totalling 30 hours per week. And some again have diffent worktimes per day/week.

 

A date table holds the daily work time per given worktime-calender:Kalender.JPG

 

Dato = Date

Timer = The total work hours that date

Kalendernr = The number for the special work-calender (ie Kalendernr "0" that has 7.5 hours per day exept friday thats 7 hours., Kalendernr "85" has 5 hours per day - not shown above, Kalendernr xx has other work hours etc.),

 

The Calendertable is joined with the employee-table:

Medarbejder.JPG

 

Medarb nr. = the employee number

Kalendernr the joined field with the Calender-table

 

I have af period-slicer joined with the date table controlling the date interval and it works.

 

The problem is that the calculated value i get in the field "Timer" in the date table is only for one person - because the date table (workcalender) only hold the data once. I somehow have to multiply that with the number of employees with that given Work-calender - or ?.

 

Any ideas...

12 REPLIES 12
Greg_Deckler
Super User
Super User

I can't get to the answer for this with the information provided, can you supply some of your raw data or a sample of mocked up data? It sort of sounds like you are doing a join in M and perhaps you need to import them as separate tables and relate them properly in order to get the effect that you want.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi smoupre

 

Here's a mock up enclosed.https://www.dropbox.com/s/a1vn650ta4j0u3y/Eksempel%20til%20BI%20Community.pbix?dl=0

 

I have changed the fieldnames etc to english 🙂

 

As you se I only get the value of total hours per week (97). Not the total value for all employees (196).

 

Udklip.JPG

 

And another problem is, that it doesnt show the values per employee  group but the same value for all groups.

 

NyUdklip.JPG

No, when it's set both ways i get the same values like you (correct values) but the total is wrong.

 

The total is 97 but has to be 196 ?

 

 

I believe the reason is because there does not exist any relationship between the Count of employees and the number of hours they have worked.

 

(In your excel file, you are multiplying total number of employees per calendar into the total hours per calendar to get the total value).

 

To replicate the same behaviour I have added a new column "Emloyee Total Hours" with the following formula:

 

Employee Total hours = CALCULATE(SUM(WorktimeCalender[Hours]),WorkTimeCalendarName[Calender no] = EARLIER(Employee[Calenderno]))

Screenshot - 4_7_2017 , 3_19_36 PM.png

 

Then, when I use to total with this column, here is what I see:

 

Screenshot - 4_7_2017 , 3_19_41 PM.png

 

Hope this achieves what you want 🙂

Hi ceebu

 

I'm almost there, but when i adopt the solution to my original BI project i get a much to large sum off Employees Total Hours.

 

I belive it's because the calculation is done to each employee in the Employee table. However we have a lot of Employees in the table that aren't "active". I probably need to filter thoose out in the function.

 

The tables are connected to a table holding entries of how many hours each employee have worked (HoursWorked). I probably need to filter the function so it only calculates Employees Total Hours if the employee has entries in the table with the hours worked. The field with the hours worked are called "WorkHours"

 

Does that sound right - and how to?



 The tables are connected to a table holding entries of how many hours each employee have worked (HoursWorked). I probably need to filter the function so it only calculates Employees Total Hours if the employee has entries in the table with the hours worked. The field with the hours worked are called "WorkHours"

 

Does that sound right - and how to?


 

I didnt see this table in the sample you had uploaded, so replicated the function using a calculated column. (Also no HoursWorked  in the three tables in your .pbix file) If you already had a list of days the employees haad worked, then you could use that to filter the total number of hours worked by the employee for the month.

Hi ceebu

 

No the table HoursWorked wasn't in the sample. I have several tables not included since it would be a huge task to make af sample of all the tables.Smiley Happy

 

The table Hoursworked holds entries per employee per customer they have worked on. So theres many entries per day for the same employee.

 

You are suggesting to filter using the Hoursworked table, it just can't figure out how - using a join or a function ?

 

My problem is that it calculates all hours fore all employes in the Employee table. However i only wnat it to calculate on the emplyees who have entries in the Hoursworked table.

You can easily add the Active status of the employee to the filter (visual filter or page filter or report filter) to achieve the desired result.

 

I added a new table with the Status Fields, and created a few employee records with Inactive status.

 

Screenshot - 4_12_2017 , 12_25_12 PM.png

 

Then, I added the Status as a filter in the visual:

 

Screenshot - 4_12_2017 , 12_25_44 PM.png

 

See the result with / without the filter.

 

Screenshot - 4_12_2017 , 12_24_56 PM.png

 

Download the Updated PBX file here -

Anonymous
Not applicable

I'm having some troubles with calculating total time for a column. My data has Name, Date, Start time, Finish Time, Lunch break, Total hours work, Total hours (hh"mm), comment. My data came from spreadsheets with stuff worked on them and all I had to do was change time to duration or time and filter out the spreadsheets that I didn't want from a folder and other information. I have a slicer with these filters, Name and Date = relative date is in the last 1 calendar weeks. I have a total hours work Total but I wanted to try a Total hours(hh:mm) Total for the same filter date=relative date is in the las 1 calendar weeks. Is this possible? I just want a simple calculation to add the Total hours (hh:mm) column. So that total should read 20.30 next to the 20.50 total.

Silinda_0-1594605948196.png

Thanks!

 

Hi ceebu

 

When i I download the updated file i get my "old" one. No ekstra table etc.

 

Perhaps you have oploaded the wrong one ?

 

However filtering using an "Active" field will not do the trick since employees going from Active to Inactive during the year has to be included.

 

I need to filter/calculate only on the emplyees who have entries in the Hoursworked table. That way they are included whether being Active or not (shiftet status) during the year.

I guess it would be better if you can share the sample of all your relevant tables and what you would like to accomplish - will make it easier to provide suggestions.


@HenrikH29 wrote:

Hi smoupre


 

And another problem is, that it doesnt show the values per employee  group but the same value for all groups.



 

Did you make the cross filter direction "Both" under relationships ? This is what I get what I make that change..Screenshot - 4_7_2017 , 2_59_33 PM.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.