cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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
Super User III
Super User III

Just read your post more carefully, 

 


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


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. 


www.excelwithallison.com

View solution in original post

7 REPLIES 7
Super User III
Super User III

Just read your post more carefully, 

 


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


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. 


www.excelwithallison.com

View solution in original post

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

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 
Super User III
Super User III

@Yudi333 ,

 

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

Super User III
Super User III

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


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. 


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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors