cancel
Showing results 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

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.

1 ACCEPTED SOLUTION
Community Support

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

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.

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

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.

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

Community Support

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

Announcements

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 Monthly Update - June 2024

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

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors