Beastmode calculate percentage of actuals compared to target (NOT total)

Hi,

Ultimately I want to show utilization of billable & other hours worked, as compared to their allocated hours. The kicker is I need to account for people working more than their allocated hours, i.e. it needs to show greater than 100%, so I cannot use the built-in Percent of Total graphs.

There is a set allocation (a.k.a. target, a.k.a. quota) of hours which is defined by person and by week.

For a simple example, Bob is allocated 40 hours Week 1. If Bob works 45 billable and 5 non-billable hours in Week 1, then the utilization for Week 1 would show 45/40 (112.5%) billable utilization and 5/40 (12.5%) non-billable utilization, with a total of 125% actual-to-target hours worked (50 total hours compared to the allocation of 40).

If I graph this by week on one individual, then it’s quite simple to use the formula `Performed Hours` / `Target Hours`. It’s when I do *not* graph this by week plus introduce multiple people that I run into problems. To reiterate - there is a set target of hours which is defined by person and by week. So the `Target Hours` is unique for each person-week combo. This should be fairly straightforward, however due to the need to dynamically filter on various fields like individual people or teams, category of work, customer name, dates, etc it quickly becomes unwieldy. Hopefully I’m overthinking this.

When doing the beastmode calculation, I ultimately need to be able to perform calculations on a unique week-person combo, but I can’t seem to slice & dice it properly.

Here is a sample data set for Alice. Her target hours are 40 for April 1st, 40 for May 1st, and 20 for June 1st.

If I want to see her time spent on billable work compared to her allocated hours, it should be 61% (20 + 8 + 5 + 20 + 3 + 5) / (40 + 40 + 20) = 61/100. One complicating factor is there are multiple billable lines per week.

If I want to see her time spent on education work compared to her allocated hours, it should be 15% (0 + 10 + 5) / (40 + 40 + 20). One complicating factor is there is a missing education line in Week 1, but we still need to factor in her Week 1’s target hours of 40 into the total target hours calculation.

I greatly appreciate any help!
-Alex