Ratio-of-Sums

Key Characteristics

• ▶Aggregates a rate by summing numerator and denominator separately, then dividing
• ▶The correct alternative to averaging row-level ratios (the sum-of-ratios error)
• ▶Weights each group by its actual denominator, which average-of-ratios ignores
• ▶Only equals average-of-ratios when every group shares an identical denominator
• ▶Requires the additive numerator and denominator to survive up to the reporting grain
• ▶Applies to any ratio metric: conversion rate, AOV, cost per click, margin %, defect rate

Why It Matters

• ▶Sum-of-ratios errors are insidious—the per-row figures are correct and the rolled-up figure looks reasonable
• ▶A dashboard that averages daily rates diverges from the true period rate whenever volume varies, which is always
• ▶The discrepancy grows with denominator skew, so the headline number can be off by a large, unpredictable margin
• ▶Pre-computing a ratio at a fine grain and then aggregating that column loses the denominator weighting permanently
• ▶Storing only the ratio in a summary table—without numerator and denominator—forecloses correct rollups entirely

Example

Two campaigns: Campaign A gets 1 conversion from 100 visits (1%); Campaign B gets 90 conversions from 900 visits (10%). The true combined conversion rate is (1 + 90) / (100 + 900) = 91 / 1000 = 9.1%. The average-of-ratios approach gives (1% + 10%) / 2 = 5.5%—a very different and wrong number, because it ignores that Campaign B drove nine times the traffic. Ratio-of-sums weights each campaign by its actual denominator; average-of-ratios does not.

Coginiti Perspective

Coginiti's semantic layer (SMDL) is designed to prevent sum-of-ratios errors by keeping ratios as derived, query-time calculations over additive base measures rather than as pre-aggregated columns. The numerator and denominator are each defined as measures with a sum aggregation type, and the ratio is expressed as a calculated/compound measure—often using hidden dimensions or measures for the component expressions—so that when Semantic SQL evaluates it through MEASURE(), the engine sums the parts at the requested grain and divides last, automatically and consistently. This means the rate is always computed ratio-of-sums no matter how an analyst slices it, and the rule lives once in the governed catalog instead of being re-derived (and occasionally botched) in each report. Coginiti's ELT approach reinforces this: because atomic numerator and denominator data stay in place on the underlying platform, ratios are computed on demand at any aggregation level rather than locked into a precomputed rate that can't be correctly rolled up.

Related Concepts