skip to content

In Tableau, why does a Grand Total row differ from the sum of the rows above it?

level: seniorimportance: should knowfreq 50%

answer

  1. the total is a mark, not a footer
  2. ask whether the aggregation is additive
  3. distinct counts cannot be added up
  4. an average of averages ignores group size
  5. there is an override, and it is a trap

basics

~10 s

A Tableau grand total re-applies the aggregation to all underlying rows in scope rather than adding up the displayed values, so SUM agrees but COUNTD, AVG, MIN, MAX and ratios legitimately differ.

solid answer

~50 s

By default Tableau computes a grand total the same way it computes any other mark: it takes the rows in scope and applies the measure's aggregation to them. It does **not** add up the numbers you can see. For `SUM` the two are identical, so nobody notices. For `COUNTD` they are not — a customer who bought in three regions counts once in the total and once in each region, so the total is smaller than the sum of rows, and that is correct. The same applies to `AVG`, `MIN`, `MAX` and any ratio of aggregates, where the total is the correctly weighted figure rather than an average of averages. You can override it with **Total using**, choosing Sum to force the total to add the displayed values — but do that only when adding them is genuinely meaningful, because it can manufacture a number that is arithmetically real and analytically false.

code

text · 7 lines
text
Region     Distinct Customers   -- COUNTD(Customer ID)
East                    1,204
West                    1,190
Central                   998
----------------------------------
Sum of visible rows     3,392
Grand Total (default)   2,913   -- customers buying in 2+ regions counted once

go deeper

for a junior

Know that grand totals are switched on from the Analysis menu and that a total which does not match the visible rows is usually expected rather than broken.

for a middle

Explain the mechanism: the total re-applies the aggregation to all rows in scope. Classify aggregations as additive or not, and give a concrete distinct-count or average example of the divergence.

for a senior

Handle the ticket: prove the default is correct, show the overlap or weighting to a stakeholder, and decide between keeping the default, relabelling the measure, or adding a second explicitly additive figure. Know why forcing Sum is usually the wrong repair.

for a principal

Own how metrics are defined so this stops recurring: which measures are certified as additive, how ratios and distinct counts are documented and labelled, and where those definitions live so every workbook and every reader inherits the same reading.

## The default behaviour Grand totals and subtotals are switched on from **Analysis > Totals**. Once visible, the total is easy to misread as a footer that adds up the column. It is not. Tableau treats the total as another mark, computed at a coarser level of detail: take every underlying row within the total's scope, apply the measure's aggregation, print the result. That design is deliberate, because "apply the aggregation to all the rows" is the definition of the total that is correct for every aggregation, whereas "add up the displayed values" is correct only for additive ones. ## When the two agree, and when they cannot `SUM` and `COUNT` are additive: summing the per-row results equals aggregating all the rows at once. Totals look unremarkable, and the whole issue stays invisible until a workbook uses something else. The non-additive cases: - **`COUNTD` (distinct count).** Distinct customers per region, totalled, is the number of distinct customers overall. Anyone who purchased in two regions was counted in both rows but only once in the total. The total is *lower* than the visible sum and is the right answer to "how many customers do we have". - **`AVG`.** The total is the average over all rows, not the average of the row averages. Those differ whenever the groups have different row counts — an average of averages weights a 10-row group the same as a 10,000-row group. - **`MIN` / `MAX`.** The total is the minimum or maximum across all rows, which equals the min or max of the visible values, not their sum. Seeing a total that matches one of the rows is expected here. - **Ratios of aggregates**, such as profit ratio computed as total profit over total sales. The grand total recomputes the ratio over all rows, giving the correctly weighted overall ratio — not the mean of the row ratios. This is the single most common "the total is wrong" ticket, and the total is the number that is right. ## Overriding with Total using Tableau exposes a **Total using** option on the total (also reachable from Analysis > Totals) with choices including Automatic, Sum, Average, Minimum, Maximum, Count and Count Distinct. Automatic is the default described above. Choosing Sum forces the total to add the displayed values. That override has a narrow legitimate use — typically when the displayed values are themselves per-group results that genuinely sum, and Automatic is producing something the audience cannot interpret. Use it carefully: - Forcing Sum on an average produces the sum of averages, which is almost never a meaningful quantity. - Forcing Sum on a distinct count double-counts, restoring exactly the overlap the default removed. - Forcing Sum on a ratio adds percentages together, producing figures over 100% that will be escalated as a bug. The better move in most cases is to leave the default and **explain the total**, or restate the measure so the total reads naturally. ## Subtotals behave the same way Subtotals follow the identical rule at their own scope: aggregate the rows belonging to that section. So a nested crosstab with region subtotals and a grand total can legitimately show three levels that do not add up in either direction when the measure is non-additive. Once the underlying principle is stated, none of it is surprising. ## Two caveats worth naming **Table calculations.** A measure that is a table calculation is computed across the marks in the view rather than from raw rows, so totals for it follow the calculation's addressing rather than the row-aggregation rule described here. If a total on a running or percent-of-total measure looks odd, the cause is the calculation's scope, not this default. **Aggregations fixed at a different grain.** A calculation deliberately scoped to a level of detail other than the view's will hold that scope in the total row too, which can make the total look inconsistent with the rows even for a SUM. That is the calculation doing what it was told. ## How to handle it in an interview or a ticket State the rule first — totals aggregate the rows, they do not add the display. Then classify the measure: additive or not. Then decide whether the default answers the business question. Most of the time the correct response to "the total is wrong" is to demonstrate that the total is right and the reader's mental model of it is not: show the distinct-customer overlap, or show the weighted ratio against the unweighted mean. Reaching straight for **Total using > Sum** to make the column tie out is the response that signals a weak candidate, because it fixes the appearance and breaks the number.

  • A stakeholder insists the grand total must equal the sum of the visible rows. How do you respond?
    Establish first whether adding them is meaningful. For a distinct count or a ratio it is not, and Total using Sum would produce a number that is arithmetically real and analytically false. Demonstrate the overlap or the weighting with a small example, then either keep the default with a clear label, or add a separate explicitly-labelled additive measure alongside it so both readings are visible.
  • Why does a subtotal in a nested Tableau crosstab sometimes disagree with both the rows and the grand total?
    Subtotals use the same rule at their own scope: they aggregate the underlying rows for that section rather than adding the visible values. With a non-additive measure such as a distinct count, the section subtotal removes duplicates within the section and the grand total removes them across all sections, so all three levels can legitimately differ.
  • What is the Total using option, and when is choosing Sum defensible?
    It overrides how a total is computed, with choices such as Automatic, Sum, Average, Minimum and Maximum. Automatic aggregates the underlying rows. Forcing Sum is defensible when the displayed values are per-group results that genuinely add up and Automatic is producing something unreadable — for instance a measure already scoped to a fixed grain. It is indefensible for averages, ratios and distinct counts.
  • Does the grand total behave the same way for a table calculation?
    No. A table calculation is computed across the marks in the view rather than from raw rows, so its total follows the calculation's addressing and partitioning rather than the aggregate-the-rows rule. A running total or percent-of-total that looks strange in the total row is usually reflecting its own scope, which is a different diagnosis from the additive-versus-non-additive one.

Asking for the total number of distinct customers is like counting the people in a building: you count each person once, even though several of them appear on more than one floor's sign-in sheet.

saying these in an interview costs you the question

  • Calls a distinct-count total a Tableau bug
  • Reaches for Total using Sum to make the column tie out
  • Assumes an average of the rows is the overall average
  • Adds percentages together to build a total ratio
  • Cannot say which aggregations are additive

context

resolving links…