How to define simple, ratio, cumulative and derived metrics in dbt

Simple metrics add up a column. The other types are built from them: a ratio divides two metrics, a cumulative metric keeps a running total, and a derived metric combines metrics with an expression, also across time. All of them go in the metrics: list of the model, as in how to create your first semantic model.

Copy and paste

    metrics:
      # simple: one aggregation
      - name: revenue
        type: simple
        agg: sum
        expr: amount
      - name: order_count
        type: simple
        agg: count
        expr: 1
      - name: shipped_revenue
        type: simple
        agg: sum
        expr: amount
        filter: "{{ Dimension('order__status') }} = 'shipped'"

      # ratio: one metric divided by another
      - name: average_order_value
        type: ratio
        numerator: revenue
        denominator: order_count

      # cumulative: a running total, all time or reset every month
      - name: cumulative_revenue
        type: cumulative
        input_metric: revenue
        period_agg: last
      - name: revenue_mtd
        type: cumulative
        input_metric: revenue
        grain_to_date: month
        period_agg: last

      # derived: an expression over other metrics
      - name: shipped_share
        type: derived
        expr: shipped_revenue / revenue
        input_metrics:
          - name: shipped_revenue
          - name: revenue
      - name: revenue_growth_mom
        type: derived
        expr: (revenue - revenue_last_month) / revenue_last_month
        input_metrics:
          - name: revenue
          - name: revenue
            alias: revenue_last_month
            offset_window: 1 month

The results

Six orders between January and March 2026, grouped by month:

And by country: shipped_share is 0.90 for Italy, where 470 of 520 was shipped, and 0.73 for Germany, 80 of 110.

Each type

• Simple aggregates expr with agg. A filter limits the rows, with dimensions written as {{ Dimension('entity__dimension') }}.
• Ratio computes the numerator and the denominator separately and then divides, at whatever grain you query. That gives a true average order value per month, not an average of daily averages.
• Cumulative sums its input metric over time: from the start of the data, over a moving window such as 7 days, or from the start of each period with grain_to_date: month.
• Derived combines metrics in expr. With offset_window, an input metric is read shifted in time, which gives comparisons with the previous month or the same month last year.

Settings that change the numbers

• period_agg on cumulative metrics. Grouped by month, a cumulative metric has one value per day and must pick one. The default, first, takes the first value in the month: the example showed 120, 200 and 450 for January, February and March instead of the month-end totals. period_agg: last gives the totals at the end of the month, which is usually what a report means.
• The end of the time spine. Cumulative metrics continue up to the last day of the time spine. With a spine running to the end of the year, months without orders repeat 630 until December. End the spine shortly after today, or query with --end-time.
• Offsets add a period. revenue_growth_mom returns a row for April with no value, because March’s revenue is shifted into April. January is empty too, because there is no December to compare with.

Where metrics go

Metrics that use one semantic model belong in that model’s metrics: list. Ratio, derived and cumulative metrics that combine metrics from different models go in a top-level metrics: key in any YAML file. measures and type_params belong to the older format and are deprecated.

dbt 1.12 warns that cumulative metrics cannot be represented in the OSI document it generates. The warning concerns that export only; the metrics work.

Tested with dbt Core 1.12 and MetricFlow 0.213 on DuckDB.