measures.mdx

title	Measures
description	Measures compute aggregated values across rows — counts, sums, averages, and more complex calculations like rolling windows, time shifts, and rankings.

While dimensions describe attributes of individual rows, measures compute values across rows — sums, counts, averages, and other aggregations. Measures can aggregate columns directly (like sum of revenue) or reference other measures to create compound metrics (like revenue / count).

See the measures reference for the full list of parameters and configuration options.

Defining measures

A measure specifies the SQL expression to aggregate and the aggregation type:

cubes:
  - name: orders
    sql_table: orders

    measures:
      - name: count
        type: count

      - name: total_amount
        sql: amount
        type: sum

      - name: average_amount
        sql: amount
        type: avg

cube(`orders`, {
  sql_table: `orders`,

  measures: {
    count: { type: `count` },
    total_amount: { sql: `amount`, type: `sum` },
    average_amount: { sql: `amount`, type: `avg` }
  }
})

Filtered measures

You can apply filters to a measure to create conditional aggregations. Only rows matching the filter are included:

cubes:
  - name: orders
    # ...

    measures:
      - name: count
        type: count

      - name: completed_count
        type: count
        filters:
          - sql: "{CUBE}.status = 'completed'"

cube(`orders`, {
  // ...

  measures: {
    count: { type: `count` },

    completed_count: {
      type: `count`,
      filters: [{ sql: `${CUBE}.status = 'completed'` }]
    }
  }
})

When completed_count is queried, Cube generates SQL with a CASE expression:

SELECT
  COUNT(CASE WHEN (orders.status = 'completed') THEN 1 END) AS completed_count
FROM orders

Calculated measures

Calculated measures perform calculations on other measures using SQL functions and operators. They provide a way to decompose complex metrics (e.g., ratios or percents) into formulas involving simpler measures.

Referencing measures in the same cube

cubes:
  - name: orders
    # ...

    measures:
      - name: count
        type: count

      - name: completed_count
        type: count
        filters:
          - sql: "{CUBE}.status = 'completed'"

      - name: completed_ratio
        sql: "1.0 * {completed_count} / NULLIF({count}, 0)"
        type: number

cube(`orders`, {
  // ...

  measures: {
    count: { type: `count` },

    completed_count: {
      type: `count`,
      filters: [{ sql: `${CUBE}.status = 'completed'` }]
    },

    completed_ratio: {
      sql: `1.0 * ${completed_count} / NULLIF(${count}, 0)`,
      type: `number`
    }
  }
})

Referencing measures from other cubes

If cubes are joined, you can reference measures across cubes. Cube generates the necessary joins automatically:

cubes:
  - name: users
    # ...

    joins:
      - name: orders
        sql: "{CUBE}.id = {orders}.user_id"
        relationship: one_to_many

    measures:
      - name: count
        type: count

      - name: purchases_to_users_ratio
        sql: "1.0 * {orders.purchases} / NULLIF({CUBE.count}, 0)"
        type: number

cube(`users`, {
  // ...

  joins: {
    orders: {
      sql: `${CUBE}.id = ${orders}.user_id`,
      relationship: `one_to_many`
    }
  },

  measures: {
    count: { type: `count` },

    purchases_to_users_ratio: {
      sql: `1.0 * ${orders.purchases} / NULLIF(${CUBE.count}, 0)`,
      type: `number`
    }
  }
})

Multi-stage measures

Multi-stage measures are calculated in two or more stages, enabling calculations on already-aggregated data. Each stage results in one or more CTEs in the generated SQL query.

Multi-stage measures are powered by Tesseract, the next-generation data modeling engine. Tesseract is currently in preview. Use the CUBEJS_TESSERACT_SQL_PLANNER environment variable to enable it.

Rolling windows

Rolling window measures calculate metrics over a moving window of time, such as cumulative counts or moving averages. Use the rolling_window parameter:

measures:
  - name: cumulative_count
    type: count
    rolling_window:
      trailing: unbounded

  - name: trailing_month_count
    sql: id
    type: count
    rolling_window:
      trailing: 1 month

Period-to-date

Period-to-date measures analyze data from the start of a period to the current date — year-to-date (YTD), quarter-to-date (QTD), or month-to-date (MTD):

measures:
  - name: revenue_ytd
    sql: revenue
    type: sum
    rolling_window:
      type: to_date
      granularity: year

  - name: revenue_qtd
    sql: revenue
    type: sum
    rolling_window:
      type: to_date
      granularity: quarter

Time shift

Time-shift measures calculate the value of another measure at a different point in time, typically for period-over-period comparisons like year-over-year growth. Use the time_shift parameter:

measures:
  - name: revenue
    sql: revenue
    type: sum

  - name: revenue_prior_year
    multi_stage: true
    sql: "{revenue}"
    type: number
    time_shift:
      - interval: 1 year
        type: prior

You can combine time shift with period-to-date for comparisons like "this year's YTD vs. last year's YTD":

measures:
  - name: revenue_ytd
    sql: revenue
    type: sum
    rolling_window:
      type: to_date
      granularity: year

  - name: revenue_prior_year_ytd
    multi_stage: true
    sql: "{revenue_ytd}"
    type: number
    time_shift:
      - time_dimension: time
        interval: 1 year
        type: prior

Time-shift measures can also be used with calendar cubes to customize how time-shifting works, e.g., to shift by retail calendar periods.

Percent of total (fixed dimension)

Use the group_by parameter to fix the inner aggregation to specific dimensions, enabling percent-of-total calculations:

measures:
  - name: revenue
    sql: revenue
    type: sum

  - name: country_revenue
    multi_stage: true
    sql: "{revenue}"
    type: sum
    group_by:
      - country

  - name: country_revenue_percentage
    multi_stage: true
    sql: "{revenue} / NULLIF({country_revenue}, 0)"
    type: number

Nested aggregates

Use the add_group_by parameter to compute an aggregate of an aggregate, e.g., the average of per-customer averages:

measures:
  - name: avg_order_value
    sql: amount
    type: avg

  - name: avg_customer_order_value
    multi_stage: true
    sql: "{avg_order_value}"
    type: avg
    add_group_by:
      - customer_id

Ranking

Use the reduce_by parameter to rank items within groups:

measures:
  - name: revenue
    sql: revenue
    type: sum

  - name: product_rank
    multi_stage: true
    order_by:
      - sql: "{revenue}"
        dir: asc
    reduce_by:
      - product
    type: rank

Filter directives

By default, a multi-stage measure inherits the query's filters into its inner aggregation stage. The filter parameter lets you override this — dropping, replacing, or augmenting filters before the inner stage runs.

This is the building block for "share of total" against an unfiltered base, measures that ignore certain filters (e.g., always-completed revenue), and measures that pin themselves to a fixed slice regardless of the surrounding query.

The filter parameter accepts four sub-parameters: mode (relative — the default — or fixed), exclude, keep_only, and include.

Drop a specific query filter. The measure ignores any filter the query places on status, but still respects every other filter:

measures:
  - name: revenue_ignoring_status
    multi_stage: true
    sql: "{revenue}"
    type: number
    filter:
      exclude:
        - status

measures: {
  revenue_ignoring_status: {
    multi_stage: true,
    sql: `${revenue}`,
    type: `number`,
    filter: {
      exclude: () => [status]
    }
  }
}

Replace a query filter. Strip the inherited filter on status and force the inner stage to filter on status = 'cancelled' instead:

measures:
  - name: revenue_cancelled
    multi_stage: true
    sql: "{revenue}"
    type: sum
    filter:
      exclude:
        - status
      include:
        - member: status
          operator: equals
          values:
            - cancelled

measures: {
  revenue_cancelled: {
    multi_stage: true,
    sql: `${revenue}`,
    type: `sum`,
    filter: {
      exclude: () => [status],
      include: [
        {
          member: `status`,
          operator: `equals`,
          values: [`cancelled`]
        }
      ]
    }
  }
}

Always compute against a fixed slice. With mode: fixed, all inherited filters are discarded and only the entries in include apply, even when the measure is referenced from another multi-stage measure:

measures:
  - name: revenue_completed_fixed
    multi_stage: true
    sql: "{revenue}"
    type: sum
    filter:
      mode: fixed
      include:
        - member: status
          operator: equals
          values:
            - completed

measures: {
  revenue_completed_fixed: {
    multi_stage: true,
    sql: `${revenue}`,
    type: `sum`,
    filter: {
      mode: `fixed`,
      include: [
        {
          member: `status`,
          operator: `equals`,
          values: [`completed`]
        }
      ]
    }
  }
}

Boolean groups. include clauses can be combined with or / and groups to express compound filters:

measures:
  - name: revenue_top_cities
    multi_stage: true
    sql: "{revenue}"
    type: sum
    filter:
      include:
        - or:
            - member: city
              operator: equals
              values:
                - NYC
            - member: city
              operator: equals
              values:
                - SF

measures: {
  revenue_top_cities: {
    multi_stage: true,
    sql: `${revenue}`,
    type: `sum`,
    filter: {
      include: [
        {
          or: [
            {
              member: `city`,
              operator: `equals`,
              values: [`NYC`]
            },
            {
              member: `city`,
              operator: `equals`,
              values: [`SF`]
            }
          ]
        }
      ]
    }
  }
}

filter can also be combined with keep_only — the inverse of exclude — to forward only filters on a chosen set of members. The same filter parameter is available on multi-stage dimensions.

For end-to-end patterns — always-on baselines, replacing a filter, pinning a baseline through chained calculations, segment-aware aggregates, and compound boolean filters — see the Filtering multi-stage measures recipe.

Conditional measures

Conditional measures depend on the value of a dimension, using the case parameter with switch dimensions:

measures:
  - name: amount_in_currency
    multi_stage: true
    case:
      switch: "{CUBE.currency}"
      when:
        - value: EUR
          sql: "{CUBE.amount_eur}"
        - value: GBP
          sql: "{CUBE.amount_gbp}"
      else:
        sql: "{CUBE.amount_usd}"
    type: number

Formatting

Use the format parameter to control how measures are displayed:

measures:
  - name: total_revenue
    sql: revenue
    type: sum
    format: currency

  - name: conversion_rate
    sql: "1.0 * {completed_count} / NULLIF({count}, 0)"
    type: number
    format: percent

Next steps

• See the measures reference for all parameters
• Learn about dimensions for grouping and filtering
• Explore pre-aggregations to accelerate measure queries
• See the period-over-period recipe for advanced time comparisons