> ## Documentation Index
> Fetch the complete documentation index at: https://docs.omni.co/llms.txt
> Use this file to discover all available pages before exploring further.

# level_of_detail

> Creates a Level of Detail aggregation, controlling the granularity at which a measure is computed.

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.

## 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`:

<CodeGroup>
  ```yaml Model theme={null}
  avg_customer_lifetime_spend:  
    sql: ${orders.amount}  
    aggregate_type: average  
    level_of_detail:  
      aggregate_type: sum  
      fixed: [customers.id]
  ```

  ```sql SQL equivalent theme={null}
  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  
  ```
</CodeGroup>

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

```yaml theme={null}
<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

<ParamField path="measure_name" type="object">
  The name of the measure. Measure names must:

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

  <Expandable title="measure_name properties" defaultOpen="true">
    <ParamField path="level_of_detail" type="object">
      Configures the level of detail calculation for this measure.

      <Expandable title="level_of_detail properties" defaultOpen="true">
        <ParamField path="aggregate_type" type="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`
        </ParamField>

        <ParamField path="grouping_strategy" type="string" required>
          The grouping strategy and the field to use to apply it, specified as `grouping_strategy: [ field_name ]`. Refer to the [Examples](#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
        </ParamField>

        <ParamField path="custom_primary_key_sql" type="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_type`s are used:

          * `sum_distinct_on`
          * `average_distinct_on`
          * `median_distinct_on`
          * `percentile_distinct_on`
        </ParamField>

        <ParamField path="cancel_query_filters" type="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`](/modeling/filters/operators/cancel-query-filter) instead.
        </ParamField>

        <ParamField path="filters" type="object">
          Applies filters to the level of detail calculation. See the [Filters documentation](/modeling/filters) for more information on Omni filter syntax.

          You can use [`cancel_query_filter: true`](/modeling/filters/operators/cancel-query-filter) 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`](#param-cancel-query-filters), which ignores all global filters.
        </ParamField>
      </Expandable>
    </ParamField>
  </Expandable>
</ParamField>

## Examples

```yaml title="Customer lifetime revenue" theme={null}
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
```

```yaml title="Fixed level of detail" theme={null}
measures:
  category_total_revenue:
    aggregate_type: sum
    sql: ${order_items.revenue}
    level_of_detail:
      aggregate_type: sum
      fixed: [products.category]
```

```yaml title="Exclude dimensions from grouping" theme={null}
measures:
  overall_average_score:
    aggregate_type: average
    sql: ${reviews.score}
    level_of_detail:
      aggregate_type: average
      always_exclude: [stores.region]
```

```yaml title="Sum distinct on with custom primary key" theme={null}
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: []
```

```yaml title="Selectively ignore a specific global filter" theme={null}
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
```

```yaml title="Replace a cancelled filter with a different value" theme={null}
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
```

```yaml title="Compare with cancel_query_filters (all-or-nothing)" theme={null}
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
```