cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

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)

enter image description here

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

enter image description here

Calendar

I have created a calendar table that list of the dates between 2015 to 2017

enter image description here

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?

1 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
)

Calculating Resource Utilization in Power BI.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Super User III
Super User III

Hi @silagy,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Microsoft
Microsoft

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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!

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
)

Calculating Resource Utilization in Power BI.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors