Utilization Report: Measuring Percent as Whole (Don't summarize measures!)

[Anonymous]

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.

Here is the current report.

I'm tearing my hair out here.  Please help!

[v-juanli-msft]

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

[Anonymous]

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]

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

[Anonymous]

For example, if I do a table by Area, then it SUMS the percentages, instead of doing an AVERAGE of the areas.

Or if I do an overall Gauge, then it SUMS the percentages instead of doing an AVERAGE of all of them.

[v-juanli-msft · Accepted solution]

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