Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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.

 

Here is the current report.

 

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's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity 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

    • Anonymous's avatar
      Anonymous
      Not 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's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity 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