Aggregate hours per week per employee then show weeks where total < 40

Power BI
1

Can this be done using a measure? I have one than sums up the hours. I also have week number in my date table. In my matrix visual, I have employee as row and week as column. I then apply the filter hours < 40. It works when I select only one week in the slicer. I see all employees whose total hours in the selected week is less than 40. But if I add a second selected week in the slicer, the filter gets applied to the total for the 2 selected weeks. The filter is like total hours for 2 weeks < 40. How can I make the filter be checked against each individual week’s total? I want it to work even if I select multiple weeks.

With 1 week selected
image

With 2 weeks selected, filter of <40 is being applied to total of the 2 weeks. I want it to be applied to each individual week’s total.
image

Best regards,

Ferdinand

2

Hi I think you can use power query to unpivot the last two columns to make it a three columns table, first column is still emloyee, second column is week and third column is hours the employees have worked.

Then apply the same method as you previously did. hope my answer helps

3

@ferdsjoseph,

Here’s one way to do this using three branched measures:

Total Hours = SUM( 'Hours Worked'[Hours Worked] )

Total Hrs Employee by Week = 
CALCULATE( [Total Hours],
    VALUES( Employees[Name] ),
    VALUES( Dates[Week Number] )
)

Total Hrs Employee by Week < 40 = 
IF( [Total Hrs Employee by Week] < 40,
    [Total Hrs Employee by Week],
    BLANK()
)

image
image.png1086×490 40.3 KB

Full solution file posted below. I hope this is helpful.

  • Brian

eDNA Forum - Aggregate Hours Less Than 40.pbix (93.6 KB)

2 Likes