https://community.tableau.com/ideas/1364
Today I found a solution for it. It needs 5 calculated fields, plus conditional formatting. The solution is based on custom total and subtotals method pioneered by Zen Master Jonathan Drummey.
We will use the Superstore data as an example, excluding a few subcategories to emulate a single subcategory in a category.
Here are the steps:
1. Union the data set with itself in the data source editor
This will add a new dimension Table Name to the data source.
2.Create 5 calculated fields
- Subcategory New
This adds 'Total' as a Subcategory.
- Sales New
This will show the equivalent calculation of Grand Total using custom grand total technique.
- Subcategory Label
This adds 'Grand Total' as a label. Otherwise it will show 'All'.
- Size
It is the Size() function. It will be used in the table calculation below.
- Hide Subtotal
This is a filter that will hide the subtotals with a single subcategory. This is a table calculation filter. We need to set Hide Subtotal to calculate along Table Down and set Size to compute along Subcategory New.
Note that we need to set the filter to be True!
3.Add two columns: green pill Sum(0)
One is for Sales New. The other is for Subcategory Label. Both are put on the Label card.
Row dimensions must include Table Name and Subcategory New, and hide the headers.
Some cleanups include coloring the text, hiding the data marks with zero opacity.
Here is the workbook. Feel free to download it and improve it.
View comments