Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
silagy
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
Ashish_Mathur
Super User
Super User

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/
v-jiascu-msft
Employee
Employee

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.