10 Replies Latest reply on Sep 2, 2018 2:33 PM by Shinichiro Murakami

# Calculate Only Where Value is not 0

Hi guys,

I thought I had a reasonable understanding of Tableau until something silly like this comes up and completely baffles me! It´s supposed to be so simple but somehow it wont work.

All I´m trying to do is carry out a division where the denominator is not 0 (or greater than 0)

Please see the table in my attached workbook. All the measures are working fine apart from the 'Digital vs Onsite'. For example Bristol 2010 it should be 44% not 64%. For Leeds 2010 it should say 5% not 3%. For this to show work the calculation needs to exclude the rows where the Onsite rows are 0 i.e. the denominator.

Hope this make sense.

P.S I have a bonus question. My Year on Year calculation is hopeless too, doesn’t seem to pick up previous years numbers in the chart. I have added the previous years numbers as a Reference Line but it´s not appearing.

Would be very grateful for some help.

Thanks.

• ###### 1. Re: Calculate Only Where Value is not 0

Could you clarify how you calculated 44% and 5 %?

Thanks,

Shin

• ###### 2. Re: Calculate Only Where Value is not 0

Got that.

working on previous.

Thanks,

Shin

2 of 2 people found this helpful
• ###### 3. Re: Calculate Only Where Value is not 0

You are confused with field name 'year'.

{max([Year])-1 = 2010/12/31

{max(year(Year))} = 2010

In that sense better modify current as well.

Thanks,

Shin

2 of 2 people found this helpful
• ###### 4. Re: Calculate Only Where Value is not 0

Or easier solution is

Thanks,

Shin

1 of 1 people found this helpful
• ###### 5. Re: Calculate Only Where Value is not 0

Thanks, I think you have nailed it.

So what should the 'YoY Chg' measure calculation be? Because it this is not returning anything. I need this to show the percentage difference in change.

• ###### 6. Re: Calculate Only Where Value is not 0

Thanks, I think you have nailed it.

So what should the 'YoY Chg' measure calculation be? Because it this is not returning anything. I need this to show the percentage difference in change.

• ###### 7. Re: Calculate Only Where Value is not 0

Delta of Aggregation of Aggregation is getting complicated.

I don't know ho you want to show it.

Here is a example, but the table cacl setting needs to be modified if you need different format.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 8. Re: Calculate Only Where Value is not 0

Mike,

This will work for you:

if (sum([OnSite]))>0

then SUM(if [OnSite]>0 then [Digital] end)/sum([OnSite])

else 0

END

Thanks,

Ankit Bansal

• ###### 9. Re: Calculate Only Where Value is not 0

Hi Shin,

I just want to show the percentage difference on each bar on the year on year chart.

I tried to look for Mark answer as correct but I cant see such option anywhere

Thanks.

• ###### 10. Re: Calculate Only Where Value is not 0

As I said,

Here is one idea.

Thanks,

Shin

1 of 1 people found this helpful