Avoid double counting in reports: Check what one row represents before summing a measure.; Joining tables can repeat values, leading to inflated totals like $110 instead of $70.; Use documented grain and keys to ensure each order counts once, even with line detail.
Image: Business Insight Stack

Metric Definitions

Part of Self-service analytics

Training report creators to avoid double counting

Use an order-and-line exercise to teach reporting grain, join checks and when to refer a calculation to the dataset owner.

Desk Governance · · 2 min read

Teach report creators to ask what one row represents before summing a measure. A join can repeat a value from one table across several related rows. A plausible chart will not reveal that mistake on its own.

Use an example learners can calculate by hand

Imagine two orders. Order A totals $40 and has two lines; Order B totals $30 and has one line. The correct order total is $70.

After joining orders to order lines, the joined rows may contain $40 twice and $30 once. Summing the repeated order-total column gives $110. These are hypothetical training values, not results from a business system.

Ask learners to name each table's grain: one row per order and one row per order line. Quantity can be summed from lines if each line appears once. An order-level total must count each order once, even when a report also shows line detail.

Make the check repeatable

Before creating a measure or combining tables, write down:

  • The business event and the key that identifies it.
  • The grain of each table used.
  • The expected relationship between the tables.
  • The population and filters.
  • An agreed total for a fixed period.

If a total rises after a join, inspect the rows and relationship. DISTINCT on the amount is not a general fix: different orders may have the same amount.

The key and the measure's grain determine what should count once. A model owner may need to correct the join, supply an approved measure or aggregate detail at the appropriate level.

Practise with two different problems

First, ask learners why the joined order-total sum is too high. Then add a duplicate order line to the source and ask what changes. A repeated order total caused by a one-to-many join and a duplicated source record require different investigation. The latter needs a source-data check; the creator should flag it rather than conceal it with a report formula.

Have learners explain whether they are counting orders, lines or customers and show the keys behind a surprising figure. If they cannot explain the calculation, refer it to the dataset owner before wider use.

Keep the guardrail close to the tool

A shared dataset can provide approved measures and documented relationships, but creators still need to understand grain and filters. Looker's symmetric aggregates can avoid certain repeated calculations across joins when the model has a correctly declared relationship. That is a Looker feature with modelling requirements, not a guarantee for every tool or dataset.

End the exercise by reproducing an agreed figure before introducing a new grouping or join. Keep that comparison for report review.

More from Metric Definitions