Forum Discussion
Calculating Resource Utilization in Power BI
I am trying to calculate a resource utilization per day.
My tables are:
ResourceAllocations
- AllocationID - Int - Unique identifier of a resource allocation
- ResourceID - Int - Unique identifier of the resource
- ResourceStartAllocationTine - DateTime - The start date and time of the allocation
- ResourceEndAllocationTine - DateTime - The end date and time of the allocation
- DurationInMinutes - Int - Calculated field, with dax, showing the duration of the allocation
- ResourceWeekDay - Int - Calculated field, with Dax, showing the weekday bases on the ResourceStartAllocationTine
Measures: I have created the following measures within the ResourceAllocations table
- TotalUnavilabilityTime - Int - Calculating field: Sum the duration of all unavailability events
- TotalAllocations - Int - Calculated field: Sum the duration of all events allocation (training events)
ResourcesWorkingHours
- ResourceID - Int - Unique identifier of the resource
- DayNumberInWeek - Int - Showing the day weekday number
- WorkingStartHour - Time - Presenting the working start hour
- WorkingEndHour - Time - Presenting the working end hour
- WorkingHours - Int - the total working hours in that day
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?
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
4 Replies
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
