
1. Re: Calculating total monthly utilization
Michael Someck Jul 24, 2017 1:47 PM (in response to P R)Hi there,
I played around a bit with the workbook, and I think the issue has to do with how you are aggregating each of the measures. Certain Measures (e.g., Yearly Target Hours) need to be fixed for each employee, but the current setup was summing the Yearly Target Hours across every record, which made the number huge (and the resulting utilization percentage quite small). I'm sure there are many ways to go about this, but here's one I came up with. All it does is adjust some of your existing calculations. The calculations might not really make sense individually until you get to the final step.
1) Target Utilization needs to be prevented from being summed (since it's supposed to be constant per employee). I used: MAX([Yearly Target Hours])/1920
2) Available Hours to Work needs to be held to just one value (instead of summed across every row in the data). To do this, I just threw in a MAX to the function you already had:
MAX(IF MONTH([EmpStartDate])=MONTH([Date Val])
AND YEAR([EmpStartDate])=YEAR([Date Val])
THEN ((Datediff('day',[EmpStartDate],[Date Val]))+1)*5.16
ELSE 160
END)
3) and this is the crucial step, Available Monthly Billable Hours needs to be fixed for each employee for each month. I used the formula:
{FIXED DATETRUNC('month',[Date Val]), [Name]: [Avail Hours to Work]*[Target Utilization]}
Essentially, this is is saying, for each Name and Month, to fix the number of hours they were available to work that month (from 2 above) multiplied by the Target Utilization for that employee.
This gives you the following, which I believe is correct:
I realize this is a bit complicated, and so I apologize in advance if it isn't clear. I've also attached a workbook with these calculations, but feel free to ask questions if you have them. Or maybe someone can jump in with a clearer solution
Hope this helps!
Michael

2. Re: Calculating total monthly utilization
P R Jul 25, 2017 4:18 AM (in response to Michael Someck)Thanks!! let me go through this and compare against my data. I will be back to mark this complete or ask additional questions.

3. Re: Calculating total monthly utilization
Michael Someck Jul 25, 2017 6:50 AM (in response to P R)Sounds good!
I did notice that the changes I made broke some of your other calculations. Looking into it, it seems as if most of them are just an issue of having MIN in the formulas. Because the Target Utilization formula now has a MAX already built in, you should be able to remove MIN from the broken formulas to get them to work.
Definitely let me know if you have any questions!

4. Re: Calculating total monthly utilization
Michael Someck Jul 25, 2017 9:26 AM (in response to P R)1 of 1 people found this helpfulIt looks like the Tableau workbook has one employee (Robert) that isn't in your excel sheet. His Available Hours (152) is the difference between what the excel sheet shows and 3019.
edit: just realized you deleted that comment with the excel sheet!

5. Re: Calculating total monthly utilization
P R Jul 25, 2017 9:27 AM (in response to Michael Someck)Yes, I realized I left robert off, so I deleted it. This is perfect. Everything is correct. Thank you!!!

6. Re: Calculating total monthly utilization
Michael Someck Jul 25, 2017 9:29 AM (in response to P R)1 of 1 people found this helpfulMy pleasure! Glad to help

7. Re: Calculating total monthly utilization
P R Jul 25, 2017 9:58 AM (in response to Michael Someck)I actually posted one more question in a new thread, if you could take a look that would be great.