Tableau: Column Total and Detail, Show and Hide with a Parameter or Hierarchy

I am starting to like Twitter, as Forest Gump would say “Twitter is like a box of chocolates; you never know what your gonna get.”

This interesting challenge came in from @Big on Data ( https://twitter.com/big_on_data)

I have 2 costs (same row of data). I would like to show the total, but also allow someone to expand and see both. Ideally the 2 costs would replace the original total. Any thoughts on how to do this?

Solving with a Pivot
I have solved these crosstab problems for WYSIWYG problems before. This solution is a little hacky, but it makes for a very clean user interface.

Now to Tableau
First I created a Parameter for “Total” and “Detailed”

Second create a Grouping for Parent Measure Groups. In this instance both of the costs go into a new group for “Total Costs”.

Third, create a calculated field called “Measure Type to Show” that shows the Parent on Total or the Measure on Detailed.

IF [LevelOfDetail] = "Total"
THEN
     [Parent Measure]
ELSE //Detailed
     [Measure]
END

Now layout all of the fields on the view as follows:

For the full workbook please see here: https://public.tableau.com/shared/P4BRFQZ9C

Bonus Solve Hierarchy
Now that we have the Parent Measure Group we can create a Hierarchy, and it works with a twisty.

Oh No, I need it in Rows
Then I got the news, the data orientation cannot be altered. This has to work with Rows. I am still going to work on a better solution, this one Boolean works, but it’s U-G-L-Y.

OK, Rows It Is

Solving with Calculated Fields
First create a calculated fields for “Dim Fixed Costs”, “Dim Variable Costs” and “Dim Total Costs” as dimensions.

//Dim Total Costs
[Fixed Cost]+[Variable Cost]
//Dim Variable Costs
[Variable Cost]
//Dim Fixed Costs
[Fixed Cost]

Second, create a display field for the Sheet “Total or Detail Sheet?”

IF [LevelOfDetail] = "Total" THEN
     "$" + STR([Dim Total Costs]) + ".00"
ELSE
     "$" + STR([Dim Fixed Costs]) + ".00 | " + "$" + STR([Dim Variable Costs]) + ".00"
END

Third, create a display field for the Header “Total or Detail Header?”

IF [LevelOfDetail] = "Total" THEN
     "Total Costs"
ELSE
     "Fixed Costs" + " | " + "Variable Costs"
END

Now layout all of the fields on the view as follows:

For the full workbook please see here: https://public.tableau.com/shared/S9Z2SK4SK

Conclusion
WYSIWYG crosstabs are a fun challenge in Tableau best solved with scaffolding the data, and I can assure you this is not as bad as it gets! Thanks for coming all the way down the rabbit hole, happy querying!

Originally published at http://bigdatadave.com on February 21, 2020.