cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shawn3474 Regular Visitor
Regular Visitor

Calculating Resource Capacity

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 
IncidentIDTeam1Team2Time1Time2  Team1Available Ops Hours
65943NetworkWindows13  DBA5
54683WindowsDBA21  Network10
41632NetworkDBA11  Windows7
65421DBA 2     
65478WindowsDBA14    
65796Network 1     
         
Want to show this….       
TeamTotal/HoursPercent of Capacity      
DBA81.6      
Network30.3      
Windows60.857143      

 

6 REPLIES 6
v-xjiin-msft Super Contributor
Super Contributor

Re: Calculating Resource Capacity

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] )
)

1.PNG

 

Thanks,
Xi Jin. 

shawn3474 Regular Visitor
Regular Visitor

Re: Calculating Resource Capacity

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 Super Contributor
Super Contributor

Re: Calculating Resource Capacity

Hi @shawn3474,

 

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

 

Thanks,
Xi Jin.

shawn3474 Regular Visitor
Regular Visitor

Re: Calculating Resource Capacity

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 Super Contributor
Super Contributor

Re: Calculating Resource Capacity

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 Regular Visitor
Regular Visitor

Re: Calculating Resource Capacity

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 346 members 2,764 guests
Please welcome our newest community members: