Measures - Omni Docs

While dimensions represent attributes of individual rows, measures compute values across rows — sums, counts, averages, and other aggregations. Measures can aggregate dimensions directly (like sum of revenue) or transform other measures (like sum of profit / count of users).

Naming measures

Measure names must be unique within a view. Names may use characters a-z (no capital letters), 0-9, or underscores, and must start with a letter. Additional parameters are nested under the measure with one tab (two spaces) of indentation:

measures:
  total_revenue:                   # the name of the field/measure
    sql: ${orders.sale_price}
    aggregate_type: sum
    format: usdcurrency

Defining measures

You can define measures in two ways: using the aggregate_type parameter for standard aggregations, or writing a raw SQL aggregate in the sql parameter.

Using aggregate_type

total_revenue:
  sql: ${orders.sale_price}
  aggregate_type: sum

Using raw SQL

average_revenue:
  sql: AVG(${orders.sale_price})

Using aggregate_type is recommended when a standard aggregation fits your use case, as Omni can apply additional optimizations like symmetric aggregation to protect against join fanout. Refer to the aggregate_type parameter documentation for a list of supported aggregates.

Don’t see an aggregate you need? Use the sql parameter to define the aggregate directly.

Filtered measures

You can apply filters to a measure to create conditional aggregations. Filtered measures aggregate only the rows that match the specified filter conditions:

count_california_seniors:
  aggregate_type: count
  filters:
    age:
      greater_than_or_equal_to: 65
    state:
      is: California

Measures referencing other measures

Measures can reference other measures to create compound metrics. When a measure references another measure, the referenced measure is computed first, and the result is used in the outer calculation:

total_revenue:
  sql: ${orders.sale_price}
  aggregate_type: sum

order_count:
  aggregate_type: count

revenue_per_order:
  sql: ${total_revenue} / NULLIF(${order_count}, 0)

Semi-additive measures with omni_dimensionalize

Semi-additive measures are aggregations that can be summed across some dimensions but not all. Consider an end-of-day bank account balance. Summing this value across all days would produce an inaccurate number, since the balance represents a running total rather than an incremental change. To handle this, you need a way to pick the correct balance (for example, the most recent one) within each grouping, and then aggregate across other dimensions like account type. In Omni, you can build semi-additive measures using the omni_dimensionalize function. This function converts a measure into a dimension within the query’s grouping context, which you can then use for filtering:

dimensions:
  last_date:
    hidden: true
    sql: omni_dimensionalize(max(${created_at}))
  is_last_date:
    hidden: true
    sql: ${created_at[date]}=${last_date[date]}

measures:
  semi_additive_sum:
    sql: ${sale_price}
    aggregate_type: sum
    filters:
      is_last_date:
        is: true

From this, you can build a filtered measure that includes the boolean field as a filter.

​Naming measures

​Defining measures

​Filtered measures

​Measures referencing other measures

​Semi-additive measures with omni_dimensionalize

Naming measures

Defining measures

Filtered measures

Measures referencing other measures

Semi-additive measures with omni_dimensionalize