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
polaris3028
Helper III
Helper III

Computing Resource Utilization Per Day, Week and Month

Hi PowerBI Gurus,

 

I'm stucked and I need help. Trying to compute for utilization rate per resource, been able to figure it out if table shows with per resource, but everytime i remove the resource column in the table and instead show it per day, the sum is not correct.

 

This looks good:

ResourceDateHours SpentHours Required% Utilization
Bryan11/1/20172.67.534.67%
Bryan11/2/201727.526.67%
Bryan11/3/20172.57.533.33%
Bryan11/6/201717.513.33%
Bryan11/7/201757.566.67%
Bryan11/8/20171.57.520.00%
Bryan11/9/20173.57.546.67%
Bryan11/10/201727.526.67%
Bryan11/15/20174.57.560.00%
Bryan11/16/201767.580.00%
Bryan11/17/20174.57.560.00%
Bryan11/20/201787.5106.67%
Bryan11/21/20171.57.520.00%
Bryan11/22/20170.257.53.33%
Bryan11/27/20172.57.533.33%
Bryan Total 47.35112.542.09%
Adams11/1/20170.57.56.67%
Adams11/2/201727.526.67%
Adams11/3/20170.257.53.33%
Adams11/9/20172.257.530.00%
Adams11/10/20170.57.56.67%
Adams11/13/201737.540.00%
Adams11/14/20173.57.546.67%
Adams11/16/20170.57.56.67%
Adams11/20/201727.526.67%
Adams Total 14.567.521.48%
Grand Total 61.8518034.36%

 

 

This one is not as the Hours Required of 7.5 hours(fix for a day) is not summing up once the Resource field is removed.

DateHours SpentHours Required%Utilization
11/1/201756.17.5748.00%
11/2/201752.547.5700.53%
11/3/201732.567.5434.13%
11/6/2017137.387.51831.73%
11/7/201732.127.5428.27%
11/8/201741.677.5555.60%
11/9/201745.57.5606.67%
11/10/201721.847.5291.20%
11/13/201727.47.5365.33%
11/14/201734.257.5456.67%
11/15/201712.787.5170.40%
11/16/201741.287.5550.40%
11/17/201715.87.5210.67%
11/20/201733.697.5449.20%
11/21/201757.637.5768.40%
11/22/201741.617.5554.80%
11/23/201762.17.5828.00%
11/24/201722.527.5300.27%
11/27/201715.257.5203.33%
11/28/201732.757.5436.67%
11/29/201719.947.5265.87%
Grand Total836.71157.5531.24%

 

Please advise.

 

Thanks!

2 ACCEPTED SOLUTIONS

Wow that fixed it!  I changed the relationship to both and changed the %Utilization to use:

%Utilization = DIVIDE([#Hours Spent],SUM('Developer Date Table'[Hours Required]),0)

 

You're amazing @parry2k!

 

Here's the result that makes more sense. 🙂

Result.PNG

View solution in original post

hahaha, same here, Bud.  Thanks a lot! You're a great help..Smiley Happy

View solution in original post

31 REPLIES 31
polaris3028
Helper III
Helper III

Here's the measure i used for Required Hours,where  #RequiredHours = DISTINCTCOUNT(Table, [Date])*7.5

i think ask here is hours spent % again hours required, correct?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

yes, @parry2k.  sort of utilization rate for a resource.

Here's the link to the file that I'm working on.  Hope this helps.

 

https://drive.google.com/file/d/1v84kSThkFZHJKbIaDG2vOpCK-b9XyKvd/view?usp=sharing

 

 

ok, no worries, which tab should I focus on?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

The Utilization tab please. 🙂

 

what output your are expecting? you already have stuff on there, so not sure what is not working?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Capture.PNG

per resource it looks good, but the sum utilization for the entire day is not correct.  The GrandTotal for #Hours Required for the day is not 7.5, but should be #Resource*7.5

is this what you are looking for?

 

hours 2.PNGhours1.PNG



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Getting closer now. Already updated the report on my end.

 

View 1: CORRECT

C1.PNG

View 2: NOT CORRECT - I removed the Resource column, and the #Hours Required is only 7.5 instead of the sum total of the View 1.

C2.PNG

yes it need to be column, the file you sent me in that #Hours Required 2 is a column and that is why I used that one, seems like you are making chages at your end at the same time 😞



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Opps, sorry about that.  I restored the previous version and did as instructed.  After creating the relationship below:

r1.PNG

 

Here's the result of the table. Is there another step to take after this?

R2.PNG

 

It's not correct stil. 😞

 

 

what aggegation you are using for "Hours Required" it suppose to be SUM



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

It's SUM... and I tried to add the Hours Required field in the table to check the result....

s1.PNG

 

 

Is the relationship as shown above correct?  What should be the %Utilization after we set a new table?

@polaris3028 not sure what you are showing here, you need to show what aggregation you used in value section when you drop "Hours Required" from this new calculated table? sorry if i'm not clear what I'm asking.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

not sure either... but here's the aggregation in the table

 

S3.PNG

Wow that fixed it!  I changed the relationship to both and changed the %Utilization to use:

%Utilization = DIVIDE([#Hours Spent],SUM('Developer Date Table'[Hours Required]),0)

 

You're amazing @parry2k!

 

Here's the result that makes more sense. 🙂

Result.PNG

YAY, can breathe now. Glad to hear it is fixed. Cheers buddy and good luck 🙂



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

hahaha, same here, Bud.  Thanks a lot! You're a great help..Smiley Happy

No worries, atleast you have solution, mark it answer if you think it is resolved.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.