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 builtin 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 nonbillable hours in Week 1, then the utilization for Week 1 would show 45/40 (112.5%) billable utilization and 5/40 (12.5%) nonbillable utilization, with a total of 125% actualtotarget 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 personweek 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 weekperson 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
Comments

Hey user06510,
In order to account for the additional percentage over 100%, you may need to use a Domo dataflow to build it. There is a lot of documentation within their knowledge base on how to build one out, or you can always contact your Domo account team for added support.
If you have not built a MySQL dataflow before, then I would advise starting with this documentation here:
https://knowledge.domo.com/Prepare/Magic_Transforms/SQL_DataFlows/01Creating_an_SQL_DataFlow
Jarvis
0 
Hi Jarvis,
Thank you for your comment. While I could accomplish some of these calculations in a dataflow, that static calculation would not dynamically recalculate based on filters applied to the card, so it does not meet my needs.
Alex
0
Categories
 10.6K All Categories
 13 Getting Started in the Community
 29 Beastmode & Analytics
 2.1K Data Platform & Data Science
 59 Domo Everywhere
 2.7K Charting
 2.4K Ideas Exchange
 1.3K Connectors
 362 Workbench
 300 Use Cases & Best Practices
 499 APIs
 118 Apps
 48 News
 753 Onboarding
 1.1K 日本支部
 4 Private Company Board