I've tried asking this question in the official Tableau-forum with no success.
I am currently trying to calculate the difference between two columns, using the same dimension and measure. Basically what I want to do is having the Excel-equivalent of just a simple "difference between two columns" calculation.
I found (sort of) an answer using a previously asked question, with a lot of suggested solutions attached to it in this post. Using the results from there I came up with this:
The resulting table looks like:
Everything here looks fine where there is a number/value present for the measure [Market Val Pct Adjusted] in both PF and PF Benchmark.
However, when there is a NULL value (or missing value) in [Market Val Pct Adjusted] in the PF portfolio (Portfolio Name dimension), i.e. when there is only a valid value in the PF Benchmark dimension - instead of showing
(0.0 - 0.09) = -0.09
for the Subsector D in the Total-column, it shows positive 0.09... I suppose this is due to the fact that in the IF-clause, since there is no data available for PF, it'll result in a False and hence return SUM([Market Val Pct Adjusted]) for PF Benchmark. Changing this to negative only results in unwanted negative values elsewhere.
So far, I have already tried to use zn() functionality for the [Market Val Pct Adjusted] field, as well as trying to replace all NULLs with zeros within the table using zn(lookup(sum([[Market Val Pct Adjusted]]), 0)) with no good results.
Anyone out there having a clue on how to fix this, rather annoying, issue?
Unfortunately, I can't attach any example atm, as the data is sensitive and non-public. The only re-usable data available is the one uploaded in the previous-mentioned example link above.
Thanks,
/N
Using suggestions mentioned in comment section in the IF-clause as so:
Yields no change in the table output... And no zeros in stead of NULLs either.
