One workaround would be to create a calculated field to conver the minus numbers into positives:
IF [value] <0 Then - [value]
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
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?
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
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.
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.