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.
Dear Power BI users,
I am having issues while calculating the utilization of employees when more than one employee selected.
Expression -
DIVIDE(
CALCULATE(
SUM('Employee Working Hours'[Worked Hours]),FILTER('Employee Working Hours', 'Employee Working Hours'[Job Type] = 10)), SUM('Calendar'[Scheduled Hours]),
0 ) + 0
Expected Utilization= 100 %
Actual Result = 200%
Reason = Its summing up worked hours and dividing overall worked hours by 40. I want to calculate it on an individual level and later average of utilization for each.
The expression works well when an individual is selected but messed up when more than 1 employee are selected. Appreciate your help on this matter.
Best, Yudi
Solved! Go to Solution.
Just read your post more carefully,
@Anonymous wrote:I want to calculate it on an individual level and later average of utilization for each.
That's the key to your answer I believe. Have you tried AVERAGEX?
AVERAGEX(SUMMARIZE('Employee Working Hours', "WorkedHrs", CALCULATE(SUM('Employee Working Hours'[Worked Hours]), FILTER('Employee Working Hours', 'Employee Working Hours'[Job Type] = 10)), "SchHrs", SUM('Calendar'[Scheduled Hours])), DIVIDE([WorkedHrs], [SchHrs], 0 ) + 0
Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos.
I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.
https://sites.google.com/site/allisonkennedycv
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Just read your post more carefully,
@Anonymous wrote:I want to calculate it on an individual level and later average of utilization for each.
That's the key to your answer I believe. Have you tried AVERAGEX?
AVERAGEX(SUMMARIZE('Employee Working Hours', "WorkedHrs", CALCULATE(SUM('Employee Working Hours'[Worked Hours]), FILTER('Employee Working Hours', 'Employee Working Hours'[Job Type] = 10)), "SchHrs", SUM('Calendar'[Scheduled Hours])), DIVIDE([WorkedHrs], [SchHrs], 0 ) + 0
Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos.
I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.
https://sites.google.com/site/allisonkennedycv
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi @AllisonKennedy
It worked when I select more than one employee but has a small issue that I need to address. Sorry, I missed a small detail. Scheduled hours are present in both tables. I am not sure on what basis it considers 24 hours as a scheduled hour in our expression. For Year-Week 2020-6, scheduled hours are 40 and it's correct in calendar table but wrong in "Employee Working Hours". I tried to add one more group by column name e.g. Calendar(Week) but it didn't help.
Do you spot any issues?
Best, yudi
I was playing more on the every expression, I've added one more condition. Included all the dates from a selected week and tried to ignore any possible date selection made by employee table
@Anonymous ,
Let me know if this works.
Var __Employee = CALCULATE(
SUM('Employee Working Hours'[Worked Hours]),FILTER('Employee Working Hours', 'Employee Working Hours'[Job Type] = 10))
var _Total = CALCULATE (SUM('Calendar'[Scheduled Hours]))
return
DIVIDE(__Employee, __Total)
Hi Harsh, Thanks for quick response. Sorry to inform you that it didn't help. It gives same result. Best, Yudi
@Anonymous Hi Yudi~
You just need to use SUMX instead of SUM.
DIVIDE(
CALCULATE(
SUMX('Employee Working Hours', 'Employee Working Hours'[Worked Hours]),FILTER('Employee Working Hours', 'Employee Working Hours'[Job Type] = 10)), SUMX('Employee Working Hours', 'Calendar'[Scheduled Hours]),
0 ) + 0
Right now the SUM('Calendar'[Scheduled Hours]) just gives you 40 hours for 1 week, no matter how many employees are selected because the measure floats in the data model and is not linked to the employee table. So if you use SUMX you can force that sum to happen in the employee table and change when multiple employees are selected.
Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos.
I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.
https://sites.google.com/site/allisonkennedycv
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi Allison, Thanks for quick response. I tried it earlier but it didn't help. It still gives same result. Also, I can't use another table's field in SUMX ( E.g. SUMX('Employee Working Hours', 'Calendar'[Scheduled Hours])) Best, yudi
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |