Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am trying to calculate a resource utilization per day.
My tables are:
ResourceAllocations
Measures: I have created the following measures within the ResourceAllocations table
ResourcesWorkingHours
Calendar
I have created a calendar table that list of the dates between 2015 to 2017
Now, I am trying to calculate the resource utilization percentage in each day. This should be WorkingHoursInDay - TotalAllocationperDay + TotalunavailabitityTimePerDay
The problem is that the ResourceAllocations contains multiple events per day, so I can't Sum the working hours there because it Sum that as well.
I think I need to create a new table that summarize the TotalAllocation, TotalunavailabilityTime and TotalWorkingHours per day
Any idea or directions?
Solved! Go to Solution.
Hi @silagy,
WorkingTimePerDays is calculated column, you can get 1440 by changing the summarization to "Don't summarize". The file is here: https://1drv.ms/u/s!ArTqPk2pu-BkgT5MvD6Wbmcw5vbG.
You can try a measure like this:
Measure = DIVIDE ( [TotalAllocations] + [TotalUnAvailabilityTime], MIN ( 'rawViews Bks_DWH_ResourcesAllocations'[WorkingTimePerDay] ), 0 )
Best Regards!
Dale
Hi @silagy,
Share the download link of your file. Also, show the exact result expected.
Hi @silagy,
If you want to create a measure, you can use SUM and SUMX. Then add 'Calendar'[Date] and [ResourceID] to a table visual, which will provide the context of measure.
'Calendar'[Date] [ResourceID] [measure]
If you want more help, please provide a sample pbix file.
Best Regards!
Dale
Thanks!
I placed a table on the canvas to help explain what I need.
As you will see on the example, the WorkingTimePerDays showing 2880 which is 1440 * 2 (Because there are 2 rows on that day).
I need to get the distinct value of working time which is 1440 minutes on a single day
I would like to calculate the utilization of each resource per day.
Thanks!
Hi @silagy,
WorkingTimePerDays is calculated column, you can get 1440 by changing the summarization to "Don't summarize". The file is here: https://1drv.ms/u/s!ArTqPk2pu-BkgT5MvD6Wbmcw5vbG.
You can try a measure like this:
Measure = DIVIDE ( [TotalAllocations] + [TotalUnAvailabilityTime], MIN ( 'rawViews Bks_DWH_ResourcesAllocations'[WorkingTimePerDay] ), 0 )
Best Regards!
Dale
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |