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

[Anonymous]

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.

Best regards,

Ferdinand 

[Nathaniel_C]

Hi @Anonymous ,

You can create this measure.

Less than 40 = If( MAX(Hours[Hours])<40,MAX(Hours[Hours]),"")

You could use BLANK() instead of "". Showing a table with hours, and then only less than 40.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Anonymous]

Hi Nathaniel,

Thank you for your solution.  Unfortunately, it didn't work for me.  The granularity of my data is one record per employee per day with the number of hours worked that day.  Then I want to see which employees and in what weeks they worked less than a total of 40 hours, a week being Monday-Sunday.

I was hoping a measure or some measures could be the solution.  But since I couldn't do it that way, I ended up creating a calculated table summarizing the hours at the employee/week level.  I then added a calculated column in both tables concatenating the email & week start date.  Names aren't unique so I used email.  I then joined the two table using the calculated column.

Now I can report on the original table but using the summarized weekly hours from the calculated table to filter.

I find my current solution a bit convoluted and feels more like a workaround.  I would like to know the best practice for this type of requirement - filtering a detail table using a value from the same table but summarized at a higher granularity (in my case, weekly total).

Best Regards,

Ferdinand