7 Replies Latest reply on Mar 1, 2016 2:38 PM by ryan.angus

# Stacked column chart subtracting negative values

I have a table with an amount field containing positive and negative numbers.  I'd like to create a stacked column chart, that doesn't put the negative values below the 0 axis, i.e. the absolute value of the negatives is stacked on top of the (positive values minus the negatives).  Wow, that sounds confusing, so I'll try and explain:

Record 1income100
Record 2expenditure-10

I'd like the column to be stacked with income displayed as 90 and y displayed as 10.  Ultimately, the goal is to show the total value, with income as 1 colour and expenditure as another.  Hope this makes sense!

• ###### 1. Re: Stacked column chart subtracting negative values

Hi,

One workaround would be to create a calculated field to conver the minus numbers into positives:

IF [value] <0 Then - [value]

ELSE [value]

END

Then simply use that as your measure and pop Header 2 on colour. You can then use the normal value to label the marks, so it will show as a - number

This should give you the result required

• ###### 2. Re: Stacked column chart subtracting negative values

Hi Paul,

Thanks for the reply.  I tried a similar approach using the absolute function.  Whilst the negative values appear correctly, the positive values still show the full value.  Using the example above, income still showed as 100, whereas I need it to display as 90.

I think the way to achieve this is to sum all income, and subtract the sum of all expenditure, but I'm not sure how to do this.  Is there a formula for a calculated field to only sum certain records?

• ###### 3. Re: Stacked column chart subtracting negative values

Hi Ryan,

Oh I see now. I'm not sure how to make Tableau do that. Have you thought about using a waterfall chart? I think it will be much easier to achieve and I personally think it would be a much stronger visualisation of what you are trying to show

Kind regards,

Paul

• ###### 5. Re: Stacked column chart subtracting negative values

Hi,

PFA

Thanks ,

Shasank

• ###### 6. Re: Stacked column chart subtracting negative values

Shasank,
Not quite - imagine record 2 being overlaid on record 1 (like a stacked chart).  The issue is, technically record 2 is a negative number (which could easily be turned into a positive) which needs to be subtracted from record 1.  I.e. record 1 would appear from 0 to 90 (even though it's original value is 100) and record 2 would appear from 90 to 100.

The real challenge I believe is subtracting record 2 from record 1 to provide the value for the chart.  Is it possible to create a calculated field to sum only certain values within a table, like sum(only income records) or something?  Then I could use sum(income) - sum(expenditure) to provide the income part of the stack, and abs(expenditure) for the expenditure part of the stack.

• ###### 7. Re: Stacked column chart subtracting negative values

Hi Paul,

I've used waterfall charts in previous tasks, but it won't work in this situation, as we need to see the breakdown of the 2 record types for each year.  I've been tasked with reproducing in Tableau what is currently easily achieved in Excel.  The current chart is well understood by the consumers, and changing it isn't really feasible.  Thanks anyway.