I have attached a sample workbook with the desired view using the data provided. First, a calculation needs to be created for each Model and set it to Units.
Ex: if [Model]='Baseline' then [Units] end
if [Model]='Scen1A' then Units end
Then, a third calculation should be created which subtracts these from each other.
sum([Baseline Units]) - sum([Scen1A Units])
Notice, on Sheet 2, Model is no longer on the view. Rather, Measure Names takes it place.
Hope this helps!
variances example.twbx.zip 5.8 MB
Thanks Tracy..I'll give it a look and will followup if there are questions.
I assumed it must be easy..it's just understanding the logic Tableau needs...
Works great Tracy..thanks for the help!
If you can, please take a look at the attached screenshot. The variance calc works fine from a totals standpoint, but how can I assign the NULLs in the Baseline column to zero, such that the variance will yield a numerical result relative to the Scen2B column? The reason the Baseline column has a NULL value is that the Origin-Destination pair that exists in Scen2B, didn't exist in the Baseline. Having a numerical value in every Variance column cell is key as the variance column is sorted to determine what change between the Model Baseline and Scenario2B had the greatest impact on cost.
As this calc exists now, a sort only yields an order of change in cases where numbers existed in both Baseline & Scen2B for any Origin-Destination pair.
This is one step beyond (different base data) than the orginal question & data, but part of the same analysis.
thanks for any thoughts...
screenshot.bmp 1.8 MB
I think I have it..I wouldn't have guessed this would work, but it seems to:
The calc below is assigned to/creates a new Measure called "Baseline Shipping Cost"
IF[Model]='Baseline' THEN [Shipping Cost] end
If anyone sees an obvious flaw here, please let me know..
You could probably simplify this to:
IF[Model]='Baseline' THEN zn( [Shipping Cost]) end
zn or zero null replaces nulls with 0
That does shorten things up a bit..I wasn't aware of the zn function, good to know.
I did try it and it fails to evaluate to zero when Baseline Shipping cost is null.
I'd guess that the Shipping Cost isn't actually null until it's associated with Baseline, but the zn is only operating on [Shipping Cost] without the association???
My previous formula (logic) applies the ISNULL to (Baseline+Shipping Cost) and it seems to work.
Ah yes, didn't spot that -
zn(IF[Model]='Baseline' THEN ( [Shipping Cost]) end)
Good timing..I was just trying that option and it seems to work perfectly