Aglet

Investigate How a Pivot Calculates Its Totals

Investigate pivot totals using a dataset designed to make competing calculations differ. Unequal group sizes expose average-of-averages behavior, while overlapping identities expose repeated counting. Define the intended total first, then follow the report’s aggregation and filtering stages so the result can be explained from source records rather than visual intuition.

Build a useful investigation brief

  1. Build a non-additive fixture

    Create a small set with unequal groups or overlapping identities appropriate to the measure. Independently calculate cell values, a visible-cell sum, and the intended overall measure. Keep the alternatives explicit so a matching result can identify which calculation the report uses rather than simply proving it returns a plausible number.

  2. Trace total and filter evaluation

    Inspect whether the total reuses aggregated cells or runs a separate expression over underlying records. Record when row and column filters, hidden categories, and limits apply. Compare the population used by the total with the visible cells, especially when a display filter removes values after the measure has already been computed.

  3. Compare the proposed semantic correction

    Evaluate one implicated calculation or definition change against additive and non-additive controls. Check the rendered label and underlying-record view. A correction should preserve the intended population and aggregation, not merely reproduce a hand-added total that double-counts identities or weights groups differently from the measure readers actually need.

What to carry forward

Deliver a small source fixture, competing calculations, and the exact aggregation or population boundary that diverges. Separate computation from explanation when both need work. A supported fix makes the intended total reproducible and understandable, including cases where it legitimately differs from adding or averaging the visible pivot cells.

Technical background: Google Cloud documentation.

Keep the decision with the work.

Use a Work Item in Aglet to record the problem, the evidence you have, and the next decision. Add an owner and priority, then keep updates in the discussion so the next person can follow the reasoning.

Create an account See the product workflow