Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Resource | Date | Hours Spent | Hours Required | % Utilization |
Bryan | 11/1/2017 | 2.6 | 7.5 | 34.67% |
Bryan | 11/2/2017 | 2 | 7.5 | 26.67% |
Bryan | 11/3/2017 | 2.5 | 7.5 | 33.33% |
Bryan | 11/6/2017 | 1 | 7.5 | 13.33% |
Bryan | 11/7/2017 | 5 | 7.5 | 66.67% |
Bryan | 11/8/2017 | 1.5 | 7.5 | 20.00% |
Bryan | 11/9/2017 | 3.5 | 7.5 | 46.67% |
Bryan | 11/10/2017 | 2 | 7.5 | 26.67% |
Bryan | 11/15/2017 | 4.5 | 7.5 | 60.00% |
Bryan | 11/16/2017 | 6 | 7.5 | 80.00% |
Bryan | 11/17/2017 | 4.5 | 7.5 | 60.00% |
Bryan | 11/20/2017 | 8 | 7.5 | 106.67% |
Bryan | 11/21/2017 | 1.5 | 7.5 | 20.00% |
Bryan | 11/22/2017 | 0.25 | 7.5 | 3.33% |
Bryan | 11/27/2017 | 2.5 | 7.5 | 33.33% |
Bryan Total | 47.35 | 112.5 | 42.09% | |
Adams | 11/1/2017 | 0.5 | 7.5 | 6.67% |
Adams | 11/2/2017 | 2 | 7.5 | 26.67% |
Adams | 11/3/2017 | 0.25 | 7.5 | 3.33% |
Adams | 11/9/2017 | 2.25 | 7.5 | 30.00% |
Adams | 11/10/2017 | 0.5 | 7.5 | 6.67% |
Adams | 11/13/2017 | 3 | 7.5 | 40.00% |
Adams | 11/14/2017 | 3.5 | 7.5 | 46.67% |
Adams | 11/16/2017 | 0.5 | 7.5 | 6.67% |
Adams | 11/20/2017 | 2 | 7.5 | 26.67% |
Adams Total | 14.5 | 67.5 | 21.48% | |
Grand Total | 61.85 | 180 | 34.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.
Date | Hours Spent | Hours Required | %Utilization |
11/1/2017 | 56.1 | 7.5 | 748.00% |
11/2/2017 | 52.54 | 7.5 | 700.53% |
11/3/2017 | 32.56 | 7.5 | 434.13% |
11/6/2017 | 137.38 | 7.5 | 1831.73% |
11/7/2017 | 32.12 | 7.5 | 428.27% |
11/8/2017 | 41.67 | 7.5 | 555.60% |
11/9/2017 | 45.5 | 7.5 | 606.67% |
11/10/2017 | 21.84 | 7.5 | 291.20% |
11/13/2017 | 27.4 | 7.5 | 365.33% |
11/14/2017 | 34.25 | 7.5 | 456.67% |
11/15/2017 | 12.78 | 7.5 | 170.40% |
11/16/2017 | 41.28 | 7.5 | 550.40% |
11/17/2017 | 15.8 | 7.5 | 210.67% |
11/20/2017 | 33.69 | 7.5 | 449.20% |
11/21/2017 | 57.63 | 7.5 | 768.40% |
11/22/2017 | 41.61 | 7.5 | 554.80% |
11/23/2017 | 62.1 | 7.5 | 828.00% |
11/24/2017 | 22.52 | 7.5 | 300.27% |
11/27/2017 | 15.25 | 7.5 | 203.33% |
11/28/2017 | 32.75 | 7.5 | 436.67% |
11/29/2017 | 19.94 | 7.5 | 265.87% |
Grand Total | 836.71 | 157.5 | 531.24% |
Please advise.
Thanks!
Solved! Go to Solution.
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. 🙂
hahaha, same here, Bud. Thanks a lot! You're a great help..
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.
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.
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?
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
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.
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:
Here's the result of the table. Is there another step to take after this?
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....
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
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. 🙂
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..
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |