Calculating capacity utilization across a team

[MtlPhil]

Good afternoon,

I am new to Power BI but quite experienced at Excel - I need a quick hand to better wrap my mind around the intricacies of PowerBI.

I am looking to calculate capacity utilization for a set of resources in a team. Capacity utilization is calculated as (billable time / total time). 

Let's assume I have the following data:

Period	Resource	Total Time	Billable Time
January	Jack	10	3
January	Jill	15	2
February	Jack	10	3
February	Jill	15	7
March	Jack	10	9
March	Jill	15	7
April	Jack	10	9
April	Jill	15	9

I am looking to automate a drill down from one number for the entire team for the entire year (total utilization rate for Jack & Jill for January - April, which would be 49 / 100) to a number for the team by month (5 / 25 for January, 10 / 25 for February, 16 / 25 for March, 18 / 25 for April) to a number by individual by month 30% for Jack in January, etc.).

It seems rather straightforward, but somehow, I am coming up short.

Thank you for your help,

Philippe

[v-chuncz-msft · Accepted solution]

@MtlPhil,

You may use Matrix visual, drag [Period] and [Resource] to Rows and add measure below.

Measure =
DIVIDE ( SUM ( Table1[Billable Time] ), SUM ( Table1[Total Time] ) )