Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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!

 

 

1 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])

11.png

 

Best Regards

Maggie

View solution in original post

5 REPLIES 5
v-juanli-msft
Community Support
Community Support

Hi @Anonymous

the utilization rates (%) shouldn’t be 100%, right?

11.png

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
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.

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
Not applicable

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

Capture.JPG

 

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

 

Capture2.JPG

Hi @Anonymous

You could modify your measure "Calc Utilization" to meet your needs here.

Measure = AVERAGEX(ALLEXCEPT(table_Roster,table_Roster[Area]),[Calc Utilization])

11.png

 

Best Regards

Maggie

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.