level_of_detail - Measures

Level of Detail (LoD) fields control the granularity at which an aggregation is computed. When used as a measure, an LoD produces an aggregation of an aggregation, such as an average of averages or a percent of total.

You can also create LoD fields in the workbook! See Creating level of detail fields for more information.

How it works

The easiest way to understand an LoD measure is to read it as SQL. For example, this avg_customer_lifetime_spend LoD first sums each customer’s order amounts, then averages those per-customer totals at whatever grain the query is run. In this example, that’s by country:

avg_customer_lifetime_spend:  
  sql: ${orders.amount}  
  aggregate_type: average  
  level_of_detail:  
    aggregate_type: sum  
    fixed: [customers.id]

SELECT customers.country AS "country",  
       AVG(lod.customer_total) AS "avg_customer_lifetime_spend"
FROM customers
INNER JOIN (  
    SELECT customer_id,
           SUM(amount) AS customer_total  
      FROM orders  
  GROUP BY customer_id  
) lod ON customers.id = lod.customer_id  
GROUP BY customers.country  

The level_of_detail block defines the inner query: fixed: [customers.id] with aggregate_type: sum produces one summed row per customer — that’s the first aggregation. The measure’s aggregate_type: average then takes those per-customer totals and averages them at the query’s grain. That’s the “aggregation of an aggregation” shape: a per-customer SUM feeding into a per-country AVG. Note that the outer GROUP BY customers.country clause comes from the dimensions selected in the query, not the LoD definition itself.

Syntax

<measure_name>:
  aggregate_type: <aggregation>
  level_of_detail:
    aggregate_type: <aggregation>
    <grouping_strategy>: [<field_name>]
    custom_primary_key_sql: <field_reference>
    cancel_query_filters: <true_false>
    filters:
      <field>:
        <filter_operator>: <value>
        cancel_query_filter: <true_false>

Properties

object

The name of the measure. Measure names must:

Be unique within the view
Start with a letter
Contain only alphanumeric characters and underscores

Hide measure_name properties

object

Configures the level of detail calculation for this measure.

Hide level_of_detail properties

string

required

The type of aggregation to apply. Supports standard aggregations like sum, average, count, min, max, and distinct-on aggregations.When the following aggregations are used, the custom_primary_key_sql parameter is required:

sum_distinct_on
average_distinct_on
median_distinct_on
percentile_distinct_on

string

required

The grouping strategy and the field to use to apply it, specified as grouping_strategy: [ field_name ]. Refer to the Examples section to see an example with complete syntax.The grouping strategy must be one of the following:

always_include - Adds specified dimensions to the query’s grouping, forcing a finer level of detail
always_exclude - Removes specified dimensions from the grouping, producing a coarser aggregation
fixed - Defines an absolute level of detail, replacing all query groupings

string

A field reference that defines the key to use for deduplication when using *_distinct_on aggregate types, specified using ${} syntax. This allows you to aggregate over a different level than the view’s primary key, such as summing amounts by order ID when your view is at the order items level.This parameter is required when the following aggregate_types are used:

sum_distinct_on
average_distinct_on
median_distinct_on
percentile_distinct_on

boolean

default:"false"

When true, ignores any row filters applied in the outer query when computing the level of detail metric. Defaults to false.To selectively ignore specific filters while keeping others, use the filters parameter with cancel_query_filter instead.

object

Applies filters to the level of detail calculation. See the Filters documentation for more information on Omni filter syntax.You can use cancel_query_filter: true within a filter to selectively ignore specific global filters for this level of detail only, while keeping other global filters active. This provides granular control compared to cancel_query_filters, which ignores all global filters.

Examples

Customer lifetime revenue

measures:
  customer_lifetime_revenue:
    aggregate_type: sum
    sql: ${order_items.sale_price}
    level_of_detail:
      aggregate_type: sum
      always_include: [users.user_id]
      cancel_query_filters: true

Fixed level of detail

measures:
  category_total_revenue:
    aggregate_type: sum
    sql: ${order_items.revenue}
    level_of_detail:
      aggregate_type: sum
      fixed: [products.category]

Exclude dimensions from grouping

measures:
  overall_average_score:
    aggregate_type: average
    sql: ${reviews.score}
    level_of_detail:
      aggregate_type: average
      always_exclude: [stores.region]

Sum distinct on with custom primary key

measures:
  total_by_order:
    aggregate_type: sum
    sql: ${order_items.amount}
    level_of_detail:
      aggregate_type: sum_distinct_on
      custom_primary_key_sql: ${order_items.order_id}
      fixed: []

Selectively ignore a specific global filter

measures:
  arr_balance:
    aggregate_type: sum
    sql: ${accounts.arr_cumulative}
    level_of_detail:
      aggregate_type: max
      fixed: [accounts.logo, orders.date]
      filters:
        orders.date:
          is: ""
          cancel_query_filter: true  # Ignores global date filter for this level of detail only

Replace a cancelled filter with a different value

measures:
  california_revenue:
    aggregate_type: sum
    sql: ${order_items.revenue}
    level_of_detail:
      aggregate_type: sum
      fixed: [stores.state]
      filters:
        stores.state:
          is: California  # Replaces any global state filter with California
          cancel_query_filter: true

Compare with cancel_query_filters (all-or-nothing)

measures:
  total_lifetime_value:
    aggregate_type: sum
    sql: ${order_items.sale_price}
    level_of_detail:
      aggregate_type: sum
      always_include: [users.user_id]
      cancel_query_filters: true  # Ignores all global filters

Getting started

Manage content

Analyze & build

Visualize & present

Deliver content

Model data

Manage integrations

level_of_detail - Measures

How it works

Syntax

Properties

Examples

​How it works

​Syntax

​Properties

​Examples

How it works

Syntax

Properties

Examples