Calculating Resource Capacity

shawn3474 · ‎03-26-2018

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

v-xjiin-msft · ‎03-26-2018

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.

shawn3474 · ‎03-28-2018

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.

v-xjiin-msft · ‎04-02-2018

Hi @shawn3474,

What kind of slicer? Please share us the logic and your desired result.

Thanks,
Xi Jin.

shawn3474 · ‎04-02-2018

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.