Calculating Resource Utilization in Power BI

[silagy]

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?

[Ashish_Mathur]

Hi @silagy,

Share the download link of your file.  Also, show the exact result expected.

[v-jiascu-msft]

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

[silagy]

@v-jiascu-msft

Thanks!

Here is a link to the pbix

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!

[v-jiascu-msft · Accepted 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