Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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):
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!
Solved! Go to 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
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
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
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.
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
User | Count |
---|---|
100 | |
89 | |
82 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |