If you ever had a need to show the details for one section of a hierarchy, but not all, hopefully this post will do the trick. In this example we will use Superstore data to show the Sub-Category details of one Category, but not the other two.
You will need 2 columns, one for the overall Category and one for the details. Your data may already have a column that has a lower grain of data for some records, but not others. A business example could be the"Sales" line is only total sales, but for "Costs" we also have a secondary breakout by Cost of Goods Sold, Marketing Costs and Admin Costs. For Sales we just want to show total sales and for cost we want to show total costs AND the breakouts by cost type.
For this example we will use a table, but this also applies to bar charts and other visuals:
As you can see in the chart above, in addition to showing each Category totals, we show the details for Furniture.
As mentioned above, I created a column in the superstore data that only showed the sub-category based on a parameter.
Step 1: Bring the two fields into the view. You will get something that shows the totals for the amounts without any details, and the detailed breakout (without a total) for the other category.
Step 2: Add subtotals to your view. You now have a total amount for each category. Notice, the amounts where there are no details is now unnecessarily duplicated because subtotals are shown for all categories.
Step 3: Hide the value NULL. This will remove the original data point and only leave to subtotal. While you cannot selectively show / hide subtotals (unfortunately) this will dynamically hide all instances where there are no details in the second column leaving you with the amount shown once.
Step 4: In the Format Pane remove the word "Total" from the label making it blank. This will clear the "Total" label from the second column without details, leaving only the details labeled.
Under Analysis tab at the top, choose to show totals at the top.
All that is left now is some formatting. I chose to remove row banding, column dividers and left the row dividers. Since all your Category amounts are technically subtotals they will be bold and you can control their format separately from the details.
Thanks for reading!
I've recently stumbled on this #TinyTableauTip and wanted to share it with the world. Do you ever find yourself needing to switch a dimension on a worksheet, but end up losing all your formatting? Maybe you duplicate a sheet but need to change the dimension? Look no further.
Let's set the stage and format our worksheet:
We have sorted our dimension (sub-category) descending based on sales and increased the size of the Sub-Categories. Now if we simply want to swap Sub-Category out for State, we could drag State ontop of Sub-Category to replace it, but you will see, it clears the formatting and we lose the sort:
Now, rather than dragging a pill to replace Sub-Category, simply double click on the Sub-Category pill in the rows window. Now within the pill type "State" to replace Sub-Category. This will preserve any formatting changes and the sort:
The reason I think this works is because the formatting must be fixed to the pill, and by in-line editing and changing the dimension in the pill, you keep your formatting.
Thanks for reading!