Semi-Additive Measures
Semi-additive measures are numeric facts that can be summed across some dimensions but not others—most often, they can be added across every dimension except time—such as account balances, inventory levels, and headcounts.
Measures fall into three additivity classes. Fully additive measures (like sales amount or quantity sold) can be summed across every dimension, including time. Non-additive measures (like unit price, ratios, or percentages) cannot be meaningfully summed across any dimension. Semi-additive measures sit between: they are additive across most dimensions but not across at least one—almost always the time dimension.
The reason is that semi-additive measures represent a level or balance at a point in time rather than a flow over an interval. A bank balance of $1,000 on Monday and $1,200 on Tuesday does not mean the customer had $2,200—it means the balance was $1,000, then $1,200. To collapse the time dimension you must use a non-summing aggregate: the closing value, the opening value, or an average over the period.
Key Characteristics
- ▶Additive across most dimensions but not across at least one—almost always time
- ▶Represent a level or balance at a point in time, not a flow over an interval
- ▶Sit between fully additive measures (sum anywhere) and non-additive measures (sum nowhere)
- ▶Collapse over time with a non-summing rule: closing value, opening value, or period average
- ▶Classic examples are account balances, inventory on hand, and headcount
- ▶Require the correct time-aggregation rule to be specified explicitly
Why It Matters
- ▶Reporting tools and semantic layers default to SUM, so a semi-additive measure treated as fully additive silently inflates time-based rollups
- ▶A quarterly inventory "total" becomes the sum of 90 daily snapshots—often off by roughly 90x
- ▶Because the individual daily numbers look correct, these errors are easy to miss and hard to trace
- ▶Choosing the wrong time-collapse rule (period-end vs. average daily balance) quietly answers a different question
- ▶Inconsistent snapshot cadence (e.g., skipping weekends) corrupts averages unless explicitly handled
Example
A daily inventory snapshot fact table records on-hand quantity per product per warehouse per day. Summing on-hand quantity across products and across warehouses is correct—that's total inventory at a moment. But to report monthly inventory you cannot sum the daily snapshots; you take the end-of-month balance, or the average daily balance, depending on the business question. The measure adds across product and warehouse but not across time.
Coginiti Perspective
Coginiti's semantic model (SMDL) addresses semi-additivity at the measure definition, which is exactly where the rule belongs. Measures are declared with an explicit aggregation_type—sum, avg, max, min, count, count_distinct, median, and others, including custom—so a balance measure can be defined to average or take a period-end value over time while remaining additive elsewhere. When analysts query through Semantic SQL, the MEASURE() function applies the declared aggregation automatically, so the time-collapse rule is enforced centrally rather than re-implemented (and re-broken) in each report. This is consistent with Coginiti's emphasis on semantic governance: the correct treatment of a tricky measure is captured once in the catalog and inherited everywhere. Because Coginiti follows ELT patterns, the atomic daily snapshots remain in place on the underlying platform and are aggregated on demand, so teams can recompute end-of-period or average balances at any grain without having pre-baked a single rollup.
More in Semantic Layer & Metrics
Business Logic Layer
A business logic layer is the component of a semantic layer or data system that encodes business rules, calculations, and transformations, making them reusable and enforced across analytics.
Data Abstraction Layer
A data abstraction layer is a software or architectural component that sits between raw data sources and analytics consumers, providing unified access and hiding implementation complexity.
Data Semantics
Data semantics refers to the documented meaning, business context, and valid usage of data elements, including definitions, relationships, constraints, and governance rules.
Derived Metrics
Derived metrics are metrics calculated from other base metrics or dimensions rather than directly from raw fact tables, enabling metric composition and reducing calculation redundancy.
Dimension
A dimension is a categorical or descriptive attribute used to slice, filter, and organize metrics, such as product, region, customer segment, or date.
Governed Metrics
Governed metrics are business metrics with centrally defined calculations, owners, approval workflows, and enforced standards that ensure consistency and trustworthiness across all analytics consumers.
See Semantic Intelligence in Action
Coginiti operationalizes business meaning across your entire data estate.