Why Your Grand Total or Subtotal Isn't Working as Expected

Version 2

    Tableau does not compute Subtotals and Grand Totals as just an aggregation (Sum, Avg, etc.) of the displayed marks. Instead, Subtotals and Grand Totals are computed as a separate calculation of the Measure at a coarser level of granularity. In other words, you may be expecting the total to be computed as an aggregate of an aggregate (like a total Sum of an Avg Sales per Department), and Tableau computes Subtotals and Grand Totals as the original measure at a higher level of aggregation (for example, the Avg of Sales measure computed across all Departments).

     

    A Grand Total for columns is computed as if there there were no Dimension pills on Rows. For a SUM() measure this looks the same as a Sum of Sums, but for an AVG() measure the Grand Total is an Avg computed across every row. If you have more complex calculated fields, then it can get even more complicated. There are a variety of techniques to customize Subtotals and Grand Totals to get desired results:

     

    - To have the total show a Sum of a Count Distinct, a custom dimension can be created: Getting the Total for Count Distinct | Tableau Software.

    - Having one worksheet with the original values and a second with the totals together in a dashboard. Instructions at Customizing Grand Totals – Part 1 | Drawing with Numbers.

    - Using a technique to test for the Grand Total result and then returning a different result. See Customizing Grand Totals – Part 2 | Drawing with Numbers.

    - Using a custom table calculation and a copy of the dimension(s) on the Level of Detail Shelf. See Customizing Grand Totals – Part 2 | Drawing with Numbers.

    - Using Custom SQL to duplicate the data so there can be more control, including placing grand totals on the top or left. See Customizing Grand Totals – Part 3 | Drawing with Numbers.

    - There are some advanced cases with customizing grand totals for table calculations that don't have generalized solutions yet, usually a combination of techniques is necessary to solve those.

     

    If you are frustrated by this and want Tableau to behave differently, here's an Idea to vote for: Totals and subtotals over displayed marks instead of separate calc.