docs-mintlify/docs/data-modeling/measures.mdx
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.
</Note>A measure specifies the SQL expression to aggregate and the aggregation type:
<CodeGroup>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` }
}
})
You can apply filters to a measure to create conditional aggregations. Only rows matching the filter are included:
<CodeGroup>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 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.
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`
}
}
})
If cubes are joined, you can reference measures across cubes. Cube generates the necessary joins automatically:
<CodeGroup>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 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.
<Warning>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 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 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 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.
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
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
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
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
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