Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Unable to group the data by an individual

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

Test.PNG

 

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

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

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


Please @mention me in your reply if you want a response.

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

View solution in original post

7 REPLIES 7
AllisonKennedy
Super User
Super User

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


Please @mention me in your reply if you want a response.

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

Anonymous
Not applicable

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? 

Test.PNG

 Best, yudi

Anonymous
Not applicable

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

 

Added - "SchHrs", CALCULATE(SUM('Calendar'[Scheduled Hours]),ALL('Calendar'[Date])))

Thanks a lot @AllisonKennedy 
harshnathani
Community Champion
Community Champion

@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)

 

Anonymous
Not applicable

Hi Harsh,  Thanks for quick response. Sorry to inform you that it didn't help. It gives same result. Best, Yudi

AllisonKennedy
Super User
Super User

@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


Please @mention me in your reply if you want a response.

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

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.