4 Replies Latest reply on Mar 18, 2018 6:52 PM by ce.lin

Dual axis bar chart with line for variance

Hello All,

I'm currently have a workbook with donations and donation goal data. I've built a dual axis bar chart to show the donations and donation goal, but would like to add one of the following features

- line chart with labels showing the variance OR
- some way of showing the variance number above the bars in a clear way to as to avoid confusion on what the number is indicating.

Also, is there a way of using If statements in a tool tip? I'm trying to make it so the I can say <volunteers> had donations of <donations> which represents a (GAIN or LOSS based on if the number is positive or negative) <variance> over their goal.

I've attached the workbook I have been working on. Thanks in advance!

• 1. Re: Dual axis bar chart with line for variance

Hello Ce Lin,

Firstly, I added variance to label so that you can see that clearly.

Then, I created a loss or gain calc field and added that to the tooltip:

if [Variance] <= 0 then 'loss' else 'gain' end

If you want the variance to always appear positive you can create a calc field with th variance field wrapped in an ABS function.

• 2. Re: Dual axis bar chart with line for variance

Great, thanks for the help on that!

One other question, in Sheet 1 I'd like to have an additional calculated column that takes the variance and divides it by the Goal to get an overall % variance from Goal. When I create the calculation Variance/Goal, I get the wrong percentage though. Can you help with this/explain why?

• 3. Re: Dual axis bar chart with line for variance

Hi Ce Lin,

To jump in here, the most obvious answer is that you are doing something like:

[Variance]/[Goal]

You need it to be:

Sum([Variance])/sum([Goal])

(in the first case, Tableau will calculate at the row level and then add them all up, which will give you incorrect results)

1 of 1 people found this helpful
• 4. Re: Dual axis bar chart with line for variance

Thanks for the explanation! That was exactly what happened.