2 Replies Latest reply on Jun 18, 2018 8:09 AM by Rishu Mankotia

# Calculation of Utilization percentage over a period; monthly percentage is ok but overall is incorrect

I need help in fixing the utilization calculation for over a period in tableau.

I use the following calculation to get answer for my monthly utilization percentage:  zn(SUM([Billing Time])/(21*8*COUNTD([Assigned To])))

The assigned to field gives me the number of resources.

This gives me a correct monthly utilization percentage value but the answer is off/wrong when the filter selects overall period i.e. all months cumulative.

• ###### 1. Re: Calculation of Utilization percentage over a period; monthly percentage is ok but overall is incorrect

Hi Rishu,

1 basic issue that I see in this calculation is a usual mistake a lot of people does while doing division. Dont use sum(num/den) instead use sum(numerator)/sum(denominator) or aggregate(num)/aggregate(den). aggregate- any aggregation

If your issue is that you are looking at a value higher than 100% utilization rate, the above solution handles that.

• ###### 2. Re: Calculation of Utilization percentage over a period; monthly percentage is ok but overall is incorrect

Hi Jeevan,

Thank you but that didn't work. I basically need to create a formula that aggregates the billing time and computes the percentage for daily weekly monthly and yearly levels. The calculation is required to sum the billing time and divide it by the total hours for a given period (number of working days*resource count*daily hours).

I have achieved to put monthly and yearly formula, but the day and week level aggregation is causing a problem. Is there a way to use a dynamic date calculation which will compute the utilization percentage based on the date range selected.