1 Reply Latest reply on Jan 28, 2016 6:57 PM by Shinichiro Murakami

# Grand Total exlcuding 0s from ZN(SUM) calculation

As title says, I am using the following calculation:

ZN(LOOKUP(((([Total Billable Hours]) /([Working Days/Mo. ]*9)) /([# of Employees/Office])),0))

The issue before I used this calculation was that I would have data for employee x for Jan and March, but not Feb. So, Tableau would show Feb as blank instead of 0. Now, because I have used the ZN function I have a 0 that shows up for Feb, but it is not factored into the average for Jan-March.

If you take a look at the screenshot, you will see that there is no data for 1,2, and 4. So, if I do a running total of the average it will show as 75% ((94+56)/2) instead of 30% ((94+56)/5).

How can I get the running total to incorporate the 0s instead of omitting them? Thanks! • ###### 1. Re: Grand Total exlcuding 0s from ZN(SUM) calculation

Matt,

You can modify the formula with using appropriate sum() and countd().

sum(value)/countd(Dimention or Measure name)

Sum() should be the measure field you want to get average.

countd() should be # of Items, # of month, or something you want to divide by.  If this changes dynamically by month, you can use LOD calculation.

If you can add sample data workbook with packaged, it helps.

Shin