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

    Grand Total exlcuding 0s from ZN(SUM) calculation

    matt.kaskow

      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!

       

      Ex1.png