Calculating Resource Capacity

[shawn3474]

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]

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]

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]

Hi @shawn3474,

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

Thanks,
Xi Jin.

[shawn3474]

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.

[v-xjiin-msft]

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.

[shawn3474]

No worries.  I am closing this topic out as I am changing direction a bit.