7 Replies Latest reply on Jul 25, 2017 9:58 AM by P R

    Calculating total monthly utilization

    P R

      Tableau 10.3 Mac

       

      (Look at Sheet "Total Monthly Utilization %)

       

      I need to show a bar graph which displays total utilization % for each month.

       

      First thing I did was calculate how many hours each employee can work each month based on their target utilization, employee start date and hours in the month. I have that part down, it is the "Available Monthly Billable Hours" calculation.

       

      I need to sum "Available Monthly Billable Hours"  for each month, which will be my denominator.

       

      Then I need to sum up "Monthly hours" for each month which will be my numerator.  ("monthly hours" is how many hours they billed in the month).

       

      My final calc would be:

       

      Total monthly utilization = sum(monthly hours) / sum(available monthly billable hours)

       

      I tried to do that, but as you can see it doesn't look right.

       

      Thanks

        • 1. Re: Calculating total monthly utilization
          Michael Someck

          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:

           

          Screen Shot 2017-07-24 at 4.45.16 PM.png

           

          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

            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

              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

                It 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.

                 

                Screen Shot 2017-07-25 at 12.24.03 PM.png

                 

                edit: just realized you deleted that comment with the excel sheet!

                1 of 1 people found this helpful
                • 5. Re: Calculating total monthly utilization
                  P R

                  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

                    My pleasure! Glad to help

                    1 of 1 people found this helpful
                    • 7. Re: Calculating total monthly utilization
                      P R

                      I actually posted one more question in a new thread, if you could take a look that would be great.

                       

                      Calculating total monthly utilization for a partial month