The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello all,
I am trying to calculate the utilization rate of resources. The utilization is calculated by billable hours / capacity.
I have two tables, one of which consists of the resource name, person code, reported hours, date of work etc. and another table that consists of the resource name, person code, start of week, capacity. The tables are linked through the person code.
I have attached a picture of the example tables.
The problem I'm having is how to calculate the utilization rate, when the reported hours are reported per day, and the capacity is per week?
I have merged these columns together, so that I have table that has both the reported hours per day, and the capacity for the whole week. For this table I would like to be able to create a calculated column that would have the sum of hours per week. Below is a picture to demonstrate. Columns "Sum of Hours per Week" and "UtilizationRate" would be calculated columns.
I have not been able to create the column "Sum of Hours per Week". Is it possible to create a calculated column like this?
Thanks for any advice!
Best Regards,
Henrik
Solved! Go to Solution.
Hi,
are you sure you want merge the two tables? It will get you in trouble if you want sum the capasity, since the capasity is repeated for each workday.
You can use this dax to create a calculated column summing the work hours into the capacity-table:
Hours per week = CALCULATE ( SUM ( 'Work'[Hours] ); FILTER ( 'Work'; 'Work'[rName] = Capacity[rName] && 'Work'[Date] >= Capacity[startOfWeek] && 'Work'[Date] <= Capacity[startOfWeek] + 6 ) )
Example report
Hi,
are you sure you want merge the two tables? It will get you in trouble if you want sum the capasity, since the capasity is repeated for each workday.
You can use this dax to create a calculated column summing the work hours into the capacity-table:
Hours per week = CALCULATE ( SUM ( 'Work'[Hours] ); FILTER ( 'Work'; 'Work'[rName] = Capacity[rName] && 'Work'[Date] >= Capacity[startOfWeek] && 'Work'[Date] <= Capacity[startOfWeek] + 6 ) )
Example report
Thank you very much for your answer!
I ended up modifying the solution a little bit to fit my needs, but I used your dax calculation as a base for my solution.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
32 | |
20 | |
19 | |
15 |
User | Count |
---|---|
44 | |
36 | |
32 | |
23 | |
23 |