Forum Discussion
Utilization Report: Measuring Percent as Whole (Don't summarize measures!)
So I've struggled with this utilization report for a few weeks now, and every time I think I have it, another problem crops up.
I've got it very simple. All data pull from a few different tables, but the main one is called (for now) Append1. Here are my columns (this table is unpivotted from tables with dates as the columns):
- Full Name - The employee name
- Project - The project the employee
- WS Date - The WS dates for each project that the employee will be working
- Projected Hours - The hours for each week on each project will be working
- Measure: [Calc End Date] = CALCULATE(MAX(Append1[WS Date]))
- Measure: [Calc Start Date] = CALCULATE(MIN(Append1[WS Date]))
- Measure: [Calc Projected Hours] = CALCULATE(SUM(Append1[Projected Hours]))
- Measure: [Calc Possible Hours] = IF([Calc Start Date]=BLANK(),BLANK(),(DATEDIFF([Calc Start Date],Append1[Calc End Date],WEEK)+1)*40)
- Measure: [Calc Utilization] = CALCULATE(DIVIDE([Calc Projected Hours],[Calc Possible Hours],BLANK()))
So basically, Calc End Date & Calc Start Date figure out when the first and last date of the employee are (within a measured date slicer) and then Calc Possible Hours takes the date difference and multiplies that by 40; so they could work 40 hours per week within that date range. Calc Utilization figures out what the utilization rate (%) is.
This works great in table forms (when I cross reference it by Full Name). But when I try to aggregate it by Area (from a related table) or even in whole (what is the overall utilization rate), my measures just add up all the utilization rates (%) instead of doing an average of the utilization rates (%) which is appropriate.
I'm tearing my hair out here. Please help!
Hi Anonymous
You could modify your measure "Calc Utilization" to meet your needs here.
Measure = AVERAGEX(ALLEXCEPT(table_Roster,table_Roster[Area]),[Calc Utilization])
Best Regards
Maggie
5 Replies
- v-juanli-msft
Community Support
Hi Anonymous
the utilization rates (%) shouldn’t be 100%, right?
Measure [Calc Projected Hours] should sum Projected Hours per Project, and [Calc Start Date] should be the min date of each project, right?
Best Regards
Maggie
- AnonymousNot applicable
If the possible hours is 40 and the person has 40 hours planned, then yes, they'd be 100%.
[Calc Projected Hours] sums all the hours per person (or however I have the data cut up; by Area, by Discipline, by Project, by Person). [Calc Start Date] is the earliest date the person shows up within the date slicer. So if a person's first date in my dataset was 06/08/18, but the slicer starts at 06/11/18, then their MINDATE would be 06/11/18.
- v-juanli-msft
Community Support
Hi Anonymous
"But when I try to aggregate it by Area (from a related table) or even in whole (what is the overall utilization rate), my measures just add up all the utilization rates (%) instead of doing an average of the utilization rates (%) which is appropriate"
Look at my picture above, I add Area to the table, Is the data showing on the table correct? Or, could you show me a screenshot showing what's wrong with you?
Best Regards
Maggie