Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hola,
I'm trying to calculate how much an employee is spending time on actual projects:
'productivity' = [total hours worked on projects] / [total hours that the employee was available]
I'm given a schema like:
WorkDone = (employee_id, date, project_id, hours)
HoursAvailable = (employee_id, date, available_hours)
So to calculate productivity, I have a measure in WorkDone table:
productivity = Sum(WorkDone.hours)/Sum(HoursAvailable.hours)
In powerBi desktop, I have a page where I do a range of analysis on WorkDone, and it includes a date filter (Timeline control).
The problem: after a date range is selected, rows on WorkDone are filtered and if there is no work done by the employee in those dates, nothing is selected (0 hours worked), but the filter also propagates to HoursAvailable and I get also 0 for hours available.
I should be using ALL with FILTER in the denominator, but how to access the dates chosen in Timeline control?
Solved! Go to Solution.
I created a new AllDates(id, date) table with 1:* relationship with Tornado and 'public employee_schedule' on date column.
In my page, I use AllDates.date for filtering, which propagates to both Tornado and public 'public employee_schedule'.
Finally I use Format > Edit Interactions to avoid any other unwanted filters/slicers affecting my measure (that is shown in a KPI card).
Thanks guys for the help.
Check the filter options for the relationship between WorkDone and your Employee table.
The default behavior (filter goes bothways) can be changed when you edit the relationship.
The cross table direction is already set to single, and btw, i do need filters to propagate for other parts of the page; it's just the measure calculation that is an exception.
Can you post a screenshot of your model?
From what I can see, filters on the Tornado table do not propagate to the 'public employee' table. However, they propagate to the 'public project' table.
Is there any chance 'public project' indirectly filters the 'public employee_schedule'? This might be the cause of the observed behavior.
Anyway, taking the DAX route, you can try someting along the line:
TotalAvailableHours:=CALCULATE(
SUM( 'public employee_schedule'[hours])
, ALL(Tornado)
)
Which column do you select in the timeline control? If you want to use ALL with FILTER in the denominator, you can try to access the dates chosen with the DAX measure like below. You can replace "WorkDone" in MinDate & MaxDate with the acutal table/column you used for the timeline control.
productivity = VAR MinDate = CALCULATE ( MIN ( WorkDone[date] ), ALLSELECTED ( WorkDone ) ) VAR MaxDate = CALCULATE ( MAX ( WorkDone[date] ), ALLSELECTED ( WorkDone ) ) RETURN ( SUM ( WorkDone[hours] ) / CALCULATE ( SUM ( HoursAvailable[available_hours] ), FILTER ( ALL ( HoursAvailable ), HoursAvailable[date] >= MinDate && HoursAvailable[date] <= MaxDate ) ) )
Best Regards,
Herbert
Hey thanks @v-haibl-msft! This definitely sounds like in the right direction.
The timeline control is based on WorkDone[date].
Based on your formula, MinDate and MaxDate are calculated correctly, but the CALCULATE part doesn't return the right sum of hours which i otherwise would get from a select on my database. It's really off the range like 40 hours when a single month is chosen, which should be 21*8=168 Really out of clue ..
I tried using SUMX instead of CALCULATE to see if something wrong with the data; it would return the right sum, but it ignores the other slicer that I have for employee_id; thus calculating hours for all employees and not the chosen employee.
SUMX (
FILTER (ALL ( HoursAvailable ), HoursAvailable[date] >= MinDate && HoursAvailable[date] <= MaxDate ),
HoursAvailable[available_hours])
p.s. if i don't choose any employees the CALCULATE function returns wrong values. even if i choose an employee it simply ignores it without any updates.
I created a new AllDates(id, date) table with 1:* relationship with Tornado and 'public employee_schedule' on date column.
In my page, I use AllDates.date for filtering, which propagates to both Tornado and public 'public employee_schedule'.
Finally I use Format > Edit Interactions to avoid any other unwanted filters/slicers affecting my measure (that is shown in a KPI card).
Thanks guys for the help.
If you have got your problem solved, you could mark the right answer to close this thread.
Best Regards,
Herbert
Hallo,
I'm trying to calculate how much an employee is spending time on actual projects:
'productivity' = [total hours worked on projects] / [total hours that the employee was available]
I've given a schema like:
WorkDone = (employee_id, date, project_id, hours) HoursAvailable = (employee_id, date, available_hours)
So to calculate productivity, I have a measure in WorkDone table:
productivity = Sum(WorkDone.hours)/Sum(HoursAvailable.hours)
In powerBi desktop, I have a page where I do a range of analysis on WorkDone, and it includes a date filter (Timeline control).
The problem: after a date range is selected, rows on WorkDone are filtered and if there is no work done by the employee in those dates, nothing is selected (0 hours worked), but the filter also propagates to HoursAvailable and I get also 0 for hours available.
So I guess I should be using ALL with FILTER in the denominator, but I'm not sure how to access the dates chosen by user.
What do you guys suggest?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |