Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to calculate resource utilization to display weather a group is being over utilized. I know I can pull it in table view to display total hours used and total hours available but I want to be able to show it as a percentage of time used so that it readily sticks out and I can use conditional formatting to flag anything above 100% as red. As a note there are 5 team columns in the real data. (Team1, Team2, Team3, Team4, and Team5 each with a corresponding Time column).
Table 1 | Table 2 | |||||||
IncidentID | Team1 | Team2 | Time1 | Time2 | Team1 | Available Ops Hours | ||
65943 | Network | Windows | 1 | 3 | DBA | 5 | ||
54683 | Windows | DBA | 2 | 1 | Network | 10 | ||
41632 | Network | DBA | 1 | 1 | Windows | 7 | ||
65421 | DBA | 2 | ||||||
65478 | Windows | DBA | 1 | 4 | ||||
65796 | Network | 1 | ||||||
Want to show this…. | ||||||||
Team | Total/Hours | Percent of Capacity | ||||||
DBA | 8 | 1.6 | ||||||
Network | 3 | 0.3 | ||||||
Windows | 6 | 0.857143 |
Hi @shawn3474,
To achieve your requirement, you can try following method:
1. Use UNION() function to create a new calculated table to group the team and sum the total time:
Team = UNION ( SUMMARIZE ( 'Table 1', 'Table 1'[Team1], "Total Team", SUM ( 'Table 1'[Time1] ) ), SUMMARIZE ( 'Table 1', 'Table 1'[Team2], "Total Team2", SUM ( 'Table 1'[Time2] ) ) )
2. Create a relationship between this new calculated table and Table 2. Then create measures for your required total and percent.
Total/Hours = CALCULATE ( SUM ( 'Team'[Total Team] ), FILTER ( 'Table 2', 'Table 2'[Team1] = MAX ( 'Team'[Team1] ) ) )
Percent of Capacity = DIVIDE ( CALCULATE ( SUM ( 'Team'[Total Team] ), FILTER ( 'Table 2', 'Table 2'[Team1] = MAX ( 'Team'[Team1] ) ) ), MAX ( 'Table 2'[Available Ops Hours] ) )
Thanks,
Xi Jin.
OK I got this working. Thank you so much for the guidance. The part I am stuck on now is that in Step 1 when I create the table it tallies everything since inception. Is there a way to introduce a slicer into this? Right now I am using a filter on the query to accomplish this but I would like to be able to slice it so that I can look at last weeks data for historical purposes without having to change the query filter.
Hi @shawn3474,
What kind of slicer? Please share us the logic and your desired result.
Thanks,
Xi Jin.
So the data goes back several years. I want to be able to look at a period of time. What I have done for the interim is placed a filter on the query to only import data from the last 7 days. Ideally though, I would like to be able to adjust the time frame as needed.
Hi @shawn3474,
Sorry for the delay.
What's the relation between this time and above data? Could you please share us a sample pbix file with One Drive or Google Drive if possible?
Thanks,
Xi Jin.
No worries. I am closing this topic out as I am changing direction a bit.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
89 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |