Showing % utilization of a resource

[Mrooney]

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)

 

Thanks!

[v-caliao-msft]

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

[MFelix]

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

[Sean]

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