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

Posted by

I am starting to like Twitter, as Forst Gump would say “Twitter is like a box of chocolates; you never know what yur 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.

SegmentMeasureValue
Segment 1Revenue100
Segment 2Revenue200
Segment 3Revenue50
Segment 1Fixed Cost30
Segment 2Fixed Cost100
Segment 3Fixed Cost10
Segment 1Variable Cost20
Segment 2Variable Cost50
Segment 3Variable Cost20

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

SegmentRevenueFixed CostVariable Cost
Segment 11003020
Segment 220010050
Segment 3501020

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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s