Calculating Resource Capacity

Question

I am trying to calculate resource utilization to display weather a group is being over utilized.&nbsp; 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.&nbsp; As a note there are 5 team columns in the real data.&nbsp; (Team1, Team2, Team3, Team4, and Team5 each with a corresponding Time column).&nbsp;Table 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Table 2&nbsp;IncidentIDTeam1Team2Time1Time2&nbsp;&nbsp;Team1Available Ops Hours65943NetworkWindows13&nbsp;&nbsp;DBA554683WindowsDBA21&nbsp;&nbsp;Network1041632NetworkDBA11&nbsp;&nbsp;Windows765421DBA&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;65478WindowsDBA14&nbsp;&nbsp;&nbsp;&nbsp;65796Network&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Want to show this….&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;TeamTotal/HoursPercent of Capacity&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DBA81.6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Network30.3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Windows60.857143&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

v-xjiin-msft · Answer

Hi&nbsp;shawn3474,&nbsp;To achieve your requirement, you can try following method:&nbsp;1. Use UNION() function to create a new calculated table to group the team and sum the total time:&nbsp;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.&nbsp;Total/Hours =
CALCULATE (
    SUM ( 'Team'[Total Team] ),
    FILTER ( 'Table 2', 'Table 2'[Team1] = MAX ( 'Team'[Team1] ) )
)&nbsp;Percent of Capacity =
DIVIDE (
    CALCULATE (
        SUM ( 'Team'[Total Team] ),
        FILTER ( 'Table 2', 'Table 2'[Team1] = MAX ( 'Team'[Team1] ) )
    ),
    MAX ( 'Table 2'[Available Ops Hours] )
)&nbsp;Thanks,Xi Jin.&nbsp;

shawn3474 · Answer

OK I got this working.&nbsp; Thank you so much for the guidance.&nbsp; The part I am stuck on now is that in Step 1 when I create the table it tallies everything since inception.&nbsp; Is there a way to introduce a slicer into this?&nbsp; 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.&nbsp;&nbsp;&nbsp;

v-xjiin-msft · Answer

Hi&nbsp;shawn3474,&nbsp;What kind of slicer? Please share us the logic and your desired result.&nbsp;Thanks,Xi Jin.

shawn3474 · Answer

So the data goes back several years.&nbsp; I want to be able to look at a period of time.&nbsp;&nbsp; What I have done for the interim is placed a filter on the query to only import data from the last 7 days.&nbsp; Ideally though, I would like to be able to adjust the time frame as needed.

v-xjiin-msft · Answer

Hi&nbsp;shawn3474,&nbsp;Sorry for the delay.&nbsp;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?&nbsp;Thanks,Xi Jin.

Forum Discussion

Calculating Resource Capacity

6 Replies

Related content

PowerBi Resource-Capacity

Two capacities in the same resource group

Help with DAX formula to calculate Resource Hours based on Capacity, Resource Value, and Res_count

Calculate required Fabric Capacity

Less resource consumption

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