Total hours per employee, per week

[Anonymous]

Hi,

I've got some time data (daily) and want to check how many hours each employee has per week. My columns are Employee, Hours and Date (from Time_table). I also have a date_table with weeks, months, etc.

Visually, I can show this by using Employee on rows and Week for columns, but I need to create a measure that sums the total per week/per employee in order to use said measure as a filter. I want to create a matrix that only shows the employees and the corresponding weeks where they have less than 40 hours.

Example: Picture below shows a matrix with Employee name, Week and Hours. I want to filter this to only show employees & weeks with <40 hours. If I try a simple visual filter now (<40), it shows nothing since the total is 246. I understand why this doesn't work, but at the same time, I don't know how to make it work either 🙂

 

So I guess what I'm asking is, how do I sum data based other columns in the dataset (without having to specify which week or which employee, only that it should return totals per employee&week).

Thanks in advance (my apologies for the clunkly explanation)

[Floriankx]

Hello,

Usually you just need to Measure1:=SUM(Time_table[hours])

Then you relate the date columns.

If you add Measure2:=If([Measure1]<40;[Measure1];Blank())

This should work if you have employees and weeks filtered (or in row and column context).

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Maybe a calculated table like this could help.

Table =
SUMMARIZE (
    Table1,
    Table1[Empolyee],
    'Calendar'[Week],
    "Total", SUM ( Table1[Hours] )
)

Best Regards,

Dale