I’m reaching out to see if you can help me get unstuck on a Tableau visualization and calculation.
We are trying to calculate and visualize utilization rate as defined as a numerator (“Projects” + “Initiatives” charged time) divided by denominator (Number of timesheets submitted times 40 hours).
The screenshots below detail what is currently occurring vs what we want to show. Currently
The denominator is understated because it is identifying unique timesheets times 40 hours separately for “Projects & Initiatives” and “Remaining Time” pie chart pieces.
Results in higher inaccurate utilization rate of 73% (50,584 / 69,400). These red numbers are not what we want.
What we want
First screenshot below shows how we want utilization rate to be calculated to result in 69% (50,584 / 73,080). Bold numbers are what we want. I tried playing around with FIXED and INCLUDE Level of Detail calculations, but couldn’t get it to work as intended.
I included more supporting screenshots below to provide more context.
Any guidance would be truly appreciated!
Visual
Denominator details
This is what we want to see in Tableau. Denominator manual details
This is the current denominator calculation. c.ChargeableHours
Here are some relevant key fields. Note that “Time Charged” is the same as “Time_Logged_Project_Total” (Just renamed). Also, we don’t want to use the 73,097 total below as some individuals charge more than 40 hours in some of their weekly timesheets. We want our calculation to max at 40 as indicated in the screenshot above using unique timesheets. Key Fields
