Filter Using Calculated Weekly Measure

[crkursel]

I have a report that shows shows the hours by employee by day and with weekly totals; goal is to filter the large list to only show employees that have hours below an expected total (assume 40/week). Report is setup as follows:

Rows

Name (from reference table with all employee names)

Columns

Week Ending (from reference date table)

Date (from reference date table)

Values
Hours (actual hours by employee for the date/week ending from the data table)

I have the following measure that calculates the actual hours by week and compare to the weekly baseline hours:

WeekVar = 
--DONT CHANGE, USED FOR CONDTIIONAL FORMATTING
IF(
    CALCULATE([Hours],
        all(refDates[Date]))
- 
    Calculate([BaseHrs],
        all(refDates[Date]))
<0,1,0)

The measure works correctly when I apply conditional formatting to highlight where an employee is short on time, however I want to also use the measure to filter out employees who are OK on time.

When I apply this measure on the visual as a filter, it takes all of the time in the visual less base hour, rather than the weekly snapshot, i.e. if someone worked 45 hrs this week and 38 last week, the total (83) > 80 base so they're dropping off.

What do I need to fix?
Thanks

[v-eachen-msft]

Hi @crkursel ,

You could remove ALL() function. It returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.

[crkursel]

@kentyler  @v-eachen-msft 

Thanks for the feedback, I realize I didn't explain the situation as well as I should have so here's an example mockup in Excel.

• The measure I noted is using ALL() to get the subtotal by week (not by day) and then assign 0 (over 40) or 1 (under 40) to the employee's weekly hours. 
  • This is currently works to get conditional formatting to highlight issues, as shown above
• I then want to filter out employees from the report who do not have any issues (no weeks under 40 in the displayed data)
  • In example above, want to filter out Employee B
• However, the result also hides employee C because the total actual hours (44.3+38.5 = 82.75) are greater than the total base hours of 80

Thanks

[kentyler]

You are using "ALL()" which overrides any measure restricting the rows to be calculated to a specific week.