Intro
I initially assumed the Total rows in Tableau can only show the sum of the values above but turns out they're a lot more versatile than that!
Firstly, you use Total using to change the aggregation used for calculating the total for the field (change the header value to reflect the calculation). It can be done on a case-by-case basis or by changing the default for a measure.
However, the row can be used to show entirely different calculations! or even be blank to create gaps in the table (NULL as the calculation and a blank space as the Total header).
Possible Use Case
I recently had to create a table where the category and the sub-category were in the same field. This meant duplicating the sales values when showing Totals (see figure 2).
The only way to solve this issue without manipulating the data or writing complex LODs is to influence the calculation of the sub-totals.
Solution
One unique feature of sub-totals is that they, by nature, create a singular row to aggregate multiple rows. To achieve this, Tableau splits the parent field to aggregate the values of the child field. For example, in Figure 3, the Department is the Parent column and Category Code is the Child field. At Department level, the sub-total is always 1 row but the Category codes are multiple.
Therefore, we can use SIZE() to differentiate the sub-totals from the rest.
SIZE() : A table calculation which returns the number of rows in the partition.
Tableau Desktop and Web Authoring Help
The way we use Size() is shown in the calculation in Figure 5. We want Tableau to halve the Sales values when the Size is 1 (because that's the number of rows we have per Department) but sum the Sales normally for all other rows.
The calculation probably doesn't seem to work when first brought into the worksheet but this is because it needs to be configured (reference figure 6). Right-click on the field and select Edit Table Calculation. Select Specific Dimensions and unclick the part field and anything above it (see Andy's blog if you need a refresher on configuring table calculations).
This means that for each Department, we now halve the Sales for the sub-total!
We can also right-click on a total row and select format it to add some finishing touches and clarifications.
Warning!
Please note that if there is only one row (ie. Size is 1), then that row would also use the calculation written for the Total. In our case, furniture has been halved when it shouldn't be.
