Tableau / Intermediate: calculation and interaction scope / Lesson 7

Row/aggregate calculations and null policy

Choose calculation grain before writing formulas, and distinguish ratios of totals from aggregates of row ratios.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Separate row expressions from view-level aggregate expressions.
Define denominator and null behavior deliberately.
Validate profit ratios with exact synthetic arithmetic.

Choose evaluation grain

A row expression uses values at source-row detail, while an aggregate expression summarizes the rows contributing to a mark. A field such as Profit/Sales can create a row ratio that Tableau later aggregates; SUM(Profit)/SUM(Sales) defines a ratio at the view’s aggregate detail. They are not equivalent. First identify whether the question concerns an individual sale, an equally weighted average sale or the share of total sales retained as profit. Formatting all three as percentages hides their different meanings. A formula that compiles can still implement the wrong weighting contract.

Read a guarded aggregate ratio

Assume the three line profits are 3,9,12 with sales 12,18,30. Aggregate margin is 24/60=40%. The first expression guards the aggregate denominator and otherwise leaves a null result. It is an unexecuted Tableau calculated-field fragment. Adding a row field directly to an aggregate can produce an aggregate/nonaggregate mismatch; repair it by identifying the intended grain, not wrapping everything in MIN until the editor accepts it. An attribute needed at one value per mark must be justified by source/grouping evidence rather than chosen arbitrarily.

tableau Unexecuted aggregate-margin expression

IF SUM([Sales]) <> 0 THEN
    SUM([Profit]) / SUM([Sales])
END

Expected margin is 0.4 for the documented fixture before percentage formatting. The expression is not an engine result and needs source/view context.

Separate null, zero and no row

A zero denominator, null input and absent fact are different conditions. ZN can replace a null with zero, but it does not prove the business meaning of that replacement or create every absent domain row. An average over observed values differs from an average including invented zeros. Define whether missing profit excludes a row, flags quality failure or follows another approved rule. Keep rejected-row counts and source reconciliation. Silently converting every unknown amount to zero can improve visual completeness while understating profit and concealing a changed source schema.

Compare two exact ratios

The second program uses Fraction to compare the ratio of total profit to total sales with the equally weighted mean of row margins. It returns 2/5 versus 23/60. Both are exact arithmetic and neither should be called correct without the business question. The program does not run Tableau aggregation, rounding or null handling. Retain per-line contributions alongside the overall result so a surprising worksheet value can be traced. If the application rounds each transaction before storage, use those approved amounts in the oracle rather than recomputing a different definition from display-formatted values.

python Exact synthetic weighting comparison

from fractions import Fraction
sales = [12, 18, 30]
profit = [3, 9, 12]
overall = Fraction(sum(profit), sum(sales))
row_mean = sum((Fraction(p, s) for p, s in zip(profit, sales)), Fraction(0)) / len(sales)
print(overall)
print(row_mean)

Expected output

2/5
23/60

2/5 is total margin;23/60 weights each line equally. No Tableau or source engine is used.

Review under selections

Test unrestricted totals, Tea only, one region, a zero denominator, null profit and no matching rows. Explain which formulas are row-level and which are aggregate-level in names or documentation. Do not promote a matching Python result into Tableau engine verification. This course opens no calculated-field editor and connects to no source. A future authorized review must validate actual typing, source aggregation, filter behavior and displayed totals. A good handoff preserves the metric contract, exact expected amounts and every missing-data decision, so a syntactically accepted formula cannot silently change the question or hide invalid input.

Common errors and fixes

Average row margin is labeled total margin

Why it happens: Equal row weights replaced sales-value weighting.
How to fix it: Define and implement the intended numerator/denominator contract.
Prevent it next time: Keep unequal line amounts in the ratio fixture.

MIN is added only to silence mixed aggregation

Why it happens: The intended row and mark grains were not resolved.
How to fix it: Choose the correct calculation level and validate attribute uniqueness.
Prevent it next time: Explain every aggregation rather than treating acceptance as correctness.

Unknown profit silently becomes zero

Why it happens: Null policy was replaced by a visual-completion shortcut.
How to fix it: Apply the approved missing-data rule and expose quality counts.
Prevent it next time: Test null, zero and absent rows independently.

Check your understanding

1. Why does 2/5 differ from 23/60 for the same three lines?

Show worked solution

The first divides total profit 24 by total sales 60. The second averages three row percentages equally, giving a small sale as much weight as a large one. Both answer distinct questions. Choose a business definition before rewriting totals, and keep a name that communicates the weighting to readers.

2. Why does ZN not prove that missing profit was really zero?