---
title: Measure correctness
description: Additive and non-additive, flow and stock, ratios rather than stored averages - and the one check nothing can do for you.
updated: 2026-09-20
---

A dashboard reported an ARR of **$596,348,393**. The real figure was
**$36,384,217**. A sixteen-fold overstatement, on a KPI card, in front of a
person who was reading it as fact.

Nothing was broken. The SQL was valid, the query ran, every gate passed, and the
dashboard was internally consistent: its subtotals reconciled with its totals.
The dataset was a **lattice**, so every cell in it was an exact aggregate
computed by the warehouse.

The measure was `ending_arr_usd`: a point-in-time snapshot of ARR. The dashboard
grouped it by tenure month, and the grand-total cell summed it across 24 tenure
months. Every customer was counted once per month they existed. Sixteen times.

It was caught only because that author had hand-written a cross-check comparing
one total against another.

This page is about the class of error that incident belongs to. It is the most
important page on this site, because a wrong number that looks right is the one
failure mode Dashies cannot recover from on its own: unlike a query-time BI tool,
we ship the only copy of the numbers, so a wrong refresh stays wrong until a
human notices.

## Additive and non-additive

The property that decides almost everything: can you get the answer for a group
of rows by combining the answers for its parts?

- **Additive.** `sum`, `count`, `min`, `max`. Revenue for us plus revenue for eu
  is genuinely revenue for us and eu. These compose over any partition, so they
  can be re-aggregated safely.
- **Non-additive.** `count(distinct ...)`, `avg`, `median`, percentiles,
  `stddev`, `variance`, `mode`. Also **ratios**, and **windowed aggregates**.
  Combining the parts gives you a number that is not the answer, and it is
  usually plausible enough to be believed.

Two measured examples of what non-additive costs when it is re-summed:

- A re-summed distinct count reported **2,503 customers against a true 1,200**,
  because a customer who bought in two regions was counted in both.
- A pre-divided rate re-averaged to **77.5% where the true figure was 38.5%**.

### What Dashies does about it

A **`cube` dataset re-sums in the browser**, so it is only correct for additive
measures. Dashies therefore inspects a `cube` dataset's SQL at publish time and
**refuses to publish** if it finds `count(distinct ...)`, `avg`, a median, a
percentile, `stddev`, `variance`, or `mode(...)`. The refusal names the construct.

The same check runs earlier, as an advisory, while your AI is validating the
query:

> For a v4 "cube" dataset this SQL computes non-additive aggregates (avg(...)): a
> cube dataset re-sums pre-aggregated rows in JS, so these go silently wrong
> under viewer filters. Use mode "lattice" (exact per cell, no engine to load)
> when the dimensions are low-cardinality, or mode "rows" (row-level + DuckDB)
> for row-level detail.

This refusal is the feature, not an obstacle. The other modes exist precisely so
you can have the measure anyway. See
[Datasets and the four modes](/concepts/dataset-modes).

## Flow and stock

Additivity is not the whole story, and the $596M incident is the proof: a `sum`
is the most additive aggregate there is, and it was still wrong.

The second distinction is what the number **means over time**:

- A **flow** accumulates over a period. New ARR, hires, deposits, tickets opened,
  orders placed. Summing it across periods is the right thing to do: twelve
  months of new ARR summed is a year of new ARR.
- A **stock** is a level measured at one instant. ARR, headcount, an account
  balance, open tickets, inventory on hand. Summing it across periods counts the
  same entities once per period and produces a figure that means nothing.

The trap is that **no static check can tell them apart**. `sum(ending_arr)` and
`sum(new_arr)` are identical to any analysis of the SQL text. Only you know which
column is a level and which one accumulates.

### `stock: true`

Because only the author knows, the author declares it. Mark the measure `stock:
true` in the spec and both the authoring tool and every future publish check it:

```yaml
measures:
  ending_arr:
    agg: sum
    stock: true
    label: ARR
    unit: { kind: currency, scale: units }
```

Declaring it during validation produces this, before anything is published:

> This SQL sums active_count, which is a point-in-time STOCK column (a snapshot
> of a level at one instant - ARR, headcount, a balance, open tickets) rather
> than a per-period FLOW. Summing a stock across the time grain recounts the same
> entities every period, so the total is usually meaningless. For a current value
> read the latest period instead of sum(); for a trend use a per-period
> avg/min/max.

It is an **advisory, never a rejection**, and deliberately so: summing a latest
snapshot across a non-time dimension can be exactly what you meant. But it turns
an invisible error into a sentence you have to read.

The remedies, in the order you should consider them:

- For a **current** value, read the latest period rather than summing.
- For a **trend**, show the stock per period on a chart rather than a rolled-up
  total.
- If the sum genuinely is intentional because the rows do not overlap, say so and
  move on.

### The displayed-rollup check

There is a second arm, and it exists because of what the $596M dashboard looked
like from the inside.

When two datasets compute the same measure with the same aggregate over the same
column, and their fully rolled-up values differ, and **a tile actually shows the
differing one**, the publish report says so and gives you four facts per dataset:
the aggregate, the column, the scope, and the value.

The display condition is the load-bearing part. In that same playtest, a second
dataset had exactly the same fan-out over 18 monthly snapshots and was completely
harmless, because every tile bound to it read the measure per month through a
date dimension. A rolled-up number nobody renders cannot mislead anybody.

This check **reports information and reaches no verdict**, because two datasets
can legitimately disagree: month-to-date beside year-to-date is the obvious case,
where both values are correct and different. Read the scope and decide. The one
exception, where the reading is unambiguous and it becomes a warning, is a
displayed rolled-up `sum` of a measure you declared `stock: true` over a
multi-period grain. That is wrong under any scope.

## Ratios, not stored averages

The single most common way to bake a wrong number into a dashboard is to store an
average.

**The lesson here holds on both statement shapes and the SQL does not**, so every
statement below says which shape it is. Against the built-in `self` connection the
statement groups and aggregates, and those rows ship inside the published file.
Against a warehouse connection or the workspace's uploaded-file source it returns
one row per underlying record, carrying the columns each number is worked out
from, and Dashies works the numbers out when a reader opens the page.
[Designing the cube](/concepts/designing-the-cube) has the two shapes in full.

```sql
-- Wrong, in-file shape. Correct at this grain, wrong under every filter.
select region, sum(revenue) / sum(orders) as avg_order_value
from orders
group by region
```

That column is right for each region as stored. The moment a viewer selects two
regions, Dashies combines the two stored ratios, and an average of averages is
not an average. It is weighted by nothing in particular.

On the held shape the same mistake wears different clothes, because a statement
at record grain has nothing to average over yet: it is a ratio worked out inside
one row, `amount / item_count as avg_item_value`, and storing that is exactly as
fatal. The remedy below is the remedy for it too.

Store the two additive parts and let the ratio be computed at display time:

```sql
-- In-file shape: the two parts are two aggregates in the SELECT.
select region, sum(revenue) as revenue, count(*) as orders
from orders
group by region
```

```sql
-- Held shape: the two parts are declared, not computed here.
select region, amount as revenue
from orders
```

The held-shape statement has no `count(*)` and no `GROUP BY`, and that is the
point rather than a simplification made for the example. Write the in-file
statement against a held dataset and `orders` goes wrong twice over: the
statement has already done the counting, and the `orders` measure below then
counts the rows it returned. The card reads the number of regions.

The declarations are the same either way:

```yaml
measures:
  revenue:
    agg: sum
    unit: { kind: currency, scale: units }
  orders:
    agg: count
    unit: { kind: count }
  aov:
    ratio: { num: revenue, den: orders }
    unit: { kind: currency, scale: units }
```

A `ratio` measure is never stored. It is a display of two aggregate measures, so
the division happens after filtering, over two numbers that were each summed
correctly. The result is exact under any filter.

This is also why an exact average on a `cube` is a ratio of a sum to a count,
literally sum divided by count, rather than an `avg` column.

If you write the division into the SQL anyway, the semantic layer catches the
common shapes:

> measure `aov` divides one aggregate by another, which re-sums wrong under
> viewer filters (the 77.5%-vs-38.5% class).

## What no check can do for you

Every gate above reads the SQL text or the declarations. Two things defeat that
completely:

- **A ratio or a distinct count hidden inside a CTE or a subquery**, where the
  text check does not see it as the measure's aggregate.
- **A fan-out join.** If a join duplicates rows, `sum(amount)` is still a `sum`,
  still additive, still passes every rule, and returns a number that is a clean
  multiple of the truth.

There is no static analysis that fixes this, because whether a join fans out
depends on the data rather than the query. So the publish report raises an
**obligation** instead: whenever **any** dataset reads more than one row source
(a join, a CTE, a comma join, or a derived table), whichever shape it takes, it
names the datasets and the construct and asks you to run the check yourself.
**The held shape is not exempt and that is worth saying out loud**, because it
is the shape where the duplicated rows are the stored copy: whatever aggregates
them afterwards does so faithfully and returns the inflated number.

The shape of the check is an independent aggregate: the same measure, over the
same window, computed against the un-joined base table in your own SQL client,
compared against that measure summed over the whole dashboard. If the dashboard
is larger, the join fanned out, and every figure on the page is inflated by the
same factor.

Read the converse of an obligation carefully, because it is the part people get
backwards. **An empty `obligations` list means the dataset reads one row source,
so it cannot fan out. It is not a statement that your numbers are right.** A
mis-declared measure or a stock summed over months on a single-source dataset is
still entirely yours to catch.

Two further things worth knowing about what validation does and does not prove:

- **Validating the SQL proves it runs, not that it is correct.** It proves the
  statement survives the executor's caps, its confinement, and its timeout, which
  matters because that executor will run it unattended forever. It says nothing
  about whether the number means what you think.
- **A clean dry run is not a clean bill of health either.** It compiles,
  validates, and seeds every dataset without writing anything, which is a great
  deal. It does not know what your metrics mean.

## One more thing: whose definition is it?

If your company defines ARR in a semantic layer, a dbt metric, or a metrics
catalog, that definition is the tested, reviewed, versioned statement of what ARR
is. A measure hand-derived from physical tables bypasses all of it.

When the published dashboard then disagrees with a number the business already
trusts, the dashboard is the thing that looks wrong, and it will be blamed on
Dashies rather than on the definition it quietly reinvented.

So: take the definition from the semantic layer where one exists, per metric
rather than once per project, and record where each measure's definition came
from. The spec's `intent` field exists for exactly this and round-trips verbatim,
so a later reader can check rather than guess.

## Next

[Designing the cube](/concepts/designing-the-cube) is the practical side: writing
SQL that stays correct and stays inside the budgets.