Customizing Subtotals and Grand Totals in Tableau

In one of the viz designs, we are using a data set that already includes the running sum, which is precalculated to save us some computations.

When calculating the totals per quarter or per year, we just need the numbers in the last month of the period, namely the number in the last month of the quarter, or the numbers in the last month of the year. This requires special calculations for both subtotals and grand totals.

Tableau Zen Master Jonathon Drummey has written profoundly on this topic in 2012 which served as my source of inspirations. In his blog, he showed how to manipulate the subtotals and grand totals.

In his examples, he showed how to use right most dimension for the custom calculation of both subtotals and grand totals. The aggregation result in his example is fairly simple. In our case, we found it not enough to give correct results for both totals.

In our solution, we used all the dimensions and derived a formula that allows to calculate subtotals and grand totals differently.

Note that we used both Month and Quarter Min()/Max() to set conditions. For quarterly subtotals, we used

• Min(Month) != Max(Month) and Min(Quarter) = Max(Quarter)

For yearly totals, we used

• Min(Month) != Max(Month) and Min(Quarter) != Max(Quarter)

In case necessary, subtotals and grand totals can be calculated using different formulas. Note that formula can be further expanded to accommodate more dimensions.

The workbook for the above solution can be found here.

PS.
Note that the subtotals and grand totals seem to work using any linear aggregations. But there are cases where complex aggregations may not work such as ratio SUM()/SUM(). You will see AGG() as aggregation function in Tableau.