Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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)
Solved! Go to 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
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).
Hi @Anonymous,
Maybe a calculated table like this could help.
Table = SUMMARIZE ( Table1, Table1[Empolyee], 'Calendar'[Week], "Total", SUM ( Table1[Hours] ) )
Best Regards,
Dale
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |