Forum Discussion
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] )
)
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.