Forum Discussion
Filters wrongly changing result in measure
I have a measure that works well up until I add filters to it. However, I don't understand why the filters affect the measure the way they do and I need help figuring it out.
The measure is "FTE to Pay":
FTE to Pay =
DIVIDE ( SUM ( 'Schedule'[Total Scheduled Hours] ), [No. of Week Days] * 8 )
-- The measure calculates the FTE from the total hours However, when I add a Calculate with filters the result changes under some scenarios (and it's wrong):
FTE to Pay (with filters) =
CALCULATE(
DIVIDE( sum('Schedule'[Total Scheduled Hours]),
[No. of Week Days]*8
),
FILTER('Schedule','Schedule'[Shift Date] > DATE(2024,02,01)), -- does not calculate for data older than February 1st, 2024
FILTER('Schedule','Schedule'[Termindated?] <> "Terminated") -- stops calculating when the employee has been terminated )
-- The measure intends to calculate the FTE from the total hours Note that:
No. of week days =
CALCULATE ( COUNTA ( date[week_day_flag] ), date[week_day_flag] = "Y" )
-- The measure counts the number of week days (Monday to Friday) as per column week_day_flag
Also, tables 'Date' and 'Schedule' are linked on Calendar Date in 'Date' and Shift Date in 'Schedule'.
One of the scenarios I have identified where the result is wrong is:
The employee works the following schedule:
Monday - 8 hours
Tuesday - Day off
Wednesday - 8 hours
Thursday - 8 hours
Friday - 8 hours
Saturday - 8 hours
Sunday - day off
So, the employee is working 40 hours a week, and on a 8-day standard full-time, I should get a result of 1.0, even though the employee is off on a weekday and working instead on Saturday. The measure results are as follows:
FTE to Pay = 1.0 -- correct
FTE to Pay (with filters) = 1.25 -- wrong
I believe the reason why it gives 1.25, is that it only counts 4 weekdays on the calculation with filters instead of 5 days. However, I don't understand why it does that when the filters are not indicating that No. of Week Days should be filtered.
Any help would be greatly appreciated.
Thanks lbendlin. I resolved it by adding the filters to the table in Power Query rather than having the filters in the measure.
2 Replies
- lbendlin
Super User
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523