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

Showing % utilization of a resource

Hi, I've a fixed Excel template which is populated as per the below.

Once imported into Power BI I'm trying to show utilization per enigneer for each month/quarter (or whatever period is filtered.) If we say we want to set a limit of 80% utilization in a month, meaning no one would go over 128hrs - based on there being 160hrs in a month on average.

 

The way the sheet is split up doesn't help as a resource may have some hours under Project 1 for February and then some more under Project 3 for Feb also. So working out the total hours assigned per month and then the utilization percentage figure is provining a real pain to sort...

 

Anyone got any guidance? I've read a few posts on here but it seems really complex - hopefully showing my data below will help:(The vlaue columns is the amount of hours)

 

Example.png 

 

Thanks!

3 REPLIES 3
Sean Super Contributor
Super Contributor

Re: Showing % utilization of a resource

@MrooneyCan you copy and paste the data from the image - ignore any warning you may get - click post again! Smiley Happy

Super User III
Super User III

Re: Showing % utilization of a resource

Hi @Mrooney,

As I can understand you want to find the percentage per month / resource in the visuals you can chose the option quick calc on the value instead of the sum or count, this gives the option to calculate over total, grand total,...

If you have every values time / resource on your visual it gives what you want, you then can add a trend line wuth fixed value 80%and see who is off.

Regards
MFelix

Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Moderator v-caliao-msft
Moderator

Re: Showing % utilization of a resource

Hi @Mrooney,

 

If you want to calculated the total hours for each month or quarter, then you can use ALLEXCEPT function, it removes all context filters in the table except filters that have been applied to the specified columns. The sample DAX for you reference
MonthTotal=CALCULATE(SUM(Table[Value]),ALLEXCEPT(Table,Table[Resource],Table[Month]))

 

Reference
https://msdn.microsoft.com/en-us/library/ee634795.aspx

 

Regards,

Charlie Liao

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors