Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MrooneyCan you copy and paste the data from the image - ignore any warning you may get - click post again!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |