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

# Calculating average order value

> Define AOV as a single measure when its numerator and denominator live in the same cube, or in two fact tables at different grains.

## Use case

Average order value (AOV) — sometimes called basket size — is revenue divided by
the number of orders. It looks like a one-line calculation, but where the two
parts live decides how it is modeled:

* **[Same cube](#same-cube)** — both parts are measures of one fact table.
* **[Two fact tables](#across-two-fact-tables)** — revenue is aggregated at one
  grain (say, day/item/location) and orders are counted at another (transaction
  lines). This is the common shape in retail models.

In both cases AOV is a ratio of two aggregates, so it must be computed *after*
its parts are aggregated — never as a row-level `amount / orders` expression.

## Same cube

When both parts are measures of the same cube, define AOV as a calculated
measure that divides them:

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: orders
      sql_table: orders

      dimensions:
        - name: id
          sql: id
          type: number
          primary_key: true

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

        - name: count
          type: count

        - name: average_order_value
          sql: "{revenue} / NULLIF({count}, 0)"
          type: number
          format: currency
  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube(`orders`, {
    sql_table: `orders`,

    dimensions: {
      id: { sql: `id`, type: `number`, primary_key: true }
    },

    measures: {
      revenue: { sql: `amount`, type: `sum`, format: `currency` },
      count: { type: `count` },

      average_order_value: {
        sql: `${revenue} / NULLIF(${count}, 0)`,
        type: `number`,
        format: `currency`
      }
    }
  })
  ```
</CodeGroup>

`NULLIF` guards the division so a group with no orders returns `NULL` rather
than failing.

## Across two fact tables

Retail models usually split the two parts. Sales dollars come from a
pre-aggregated daily table (`item_location_sales`, one row per day, item and
location), while the transaction count comes from the line-item table
(`sales_line_item`, one row per transaction line). The two never join to each
other — they meet through shared `items`, `locations` and `dates` cubes, which
makes this a [multi-fact query][ref-multi-fact-views].

<Warning>
  Multi-fact views and multi-stage measures are powered by Tesseract, the
  [next-generation data modeling engine][link-tesseract]. In versions before
  v1.7.0, it was not enabled by default.
</Warning>

### 1. Define each part on the cube that owns it

The denominator counts distinct transactions and excludes exchanges and
non-store channels. Write that logic once, as measure
[`filters`][ref-measure-filters] on the line-item cube, so every consumer picks
it up by including the measure — never restate it per view:

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: sales_line_item
      sql_table: sales_line_item

      joins:
        - name: items
          sql: "{CUBE}.item_id = {items.id}"
          relationship: many_to_one
        - name: locations
          sql: "{CUBE}.location_id = {locations.id}"
          relationship: many_to_one
        - name: dates
          sql: "DATE_TRUNC('day', {CUBE}.sold_at) = {dates.date}"
          relationship: many_to_one

      dimensions:
        - name: id
          sql: id
          type: number
          primary_key: true

      measures:
        - name: transactions_without_returns
          sql: transaction_id
          type: count_distinct
          filters:
            - sql: "{CUBE}.transaction_type <> 'EXCHANGE'"
            - sql: "{CUBE}.fulfillment_channel_group IN ('IN_STORE', 'SHIP_FROM_STORE')"

    - name: item_location_sales
      sql_table: item_location_sales

      joins:
        - name: items
          sql: "{CUBE}.item_id = {items.id}"
          relationship: many_to_one
        - name: locations
          sql: "{CUBE}.location_id = {locations.id}"
          relationship: many_to_one
        - name: dates
          sql: "DATE_TRUNC('day', {CUBE}.date) = {dates.date}"
          relationship: many_to_one

      dimensions:
        - name: id
          sql: id
          type: number
          primary_key: true

      measures:
        - name: sales_amount
          sql: sales_amount
          type: sum
          format: currency
  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube(`sales_line_item`, {
    sql_table: `sales_line_item`,

    joins: {
      items: {
        sql: `${CUBE}.item_id = ${items.id}`,
        relationship: `many_to_one`
      },
      locations: {
        sql: `${CUBE}.location_id = ${locations.id}`,
        relationship: `many_to_one`
      },
      dates: {
        sql: `DATE_TRUNC('day', ${CUBE}.sold_at) = ${dates.date}`,
        relationship: `many_to_one`
      }
    },

    dimensions: {
      id: { sql: `id`, type: `number`, primary_key: true }
    },

    measures: {
      transactions_without_returns: {
        sql: `transaction_id`,
        type: `count_distinct`,
        filters: [
          { sql: `${CUBE}.transaction_type <> 'EXCHANGE'` },
          { sql: `${CUBE}.fulfillment_channel_group IN ('IN_STORE', 'SHIP_FROM_STORE')` }
        ]
      }
    }
  })

  cube(`item_location_sales`, {
    sql_table: `item_location_sales`,

    joins: {
      items: {
        sql: `${CUBE}.item_id = ${items.id}`,
        relationship: `many_to_one`
      },
      locations: {
        sql: `${CUBE}.location_id = ${locations.id}`,
        relationship: `many_to_one`
      },
      dates: {
        sql: `DATE_TRUNC('day', ${CUBE}.date) = ${dates.date}`,
        relationship: `many_to_one`
      }
    },

    dimensions: {
      id: { sql: `id`, type: `number`, primary_key: true }
    },

    measures: {
      sales_amount: { sql: `sales_amount`, type: `sum`, format: `currency` }
    }
  })
  ```
</CodeGroup>

Both facts join to the same `items`, `locations` and `dates` cubes. The `dates`
spine matters: without it the two facts have no common time member to group by,
since one is keyed by day and the other by timestamp.

### 2. Define AOV on the view

Neither cube can define AOV — neither can reference the other's measures. Define
it as a [measure of the view][ref-view-measures] and mark it
[`multi_stage`][ref-multi-stage]:

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  views:
    - name: retail_analysis
      cubes:
        - join_path: item_location_sales
          includes:
            - sales_amount
        - join_path: sales_line_item
          includes:
            - transactions_without_returns
        - join_path: dates
          includes:
            - date
        - join_path: items
          includes:
            - department
        - join_path: locations
          includes:
            - region

      measures:
        - name: aov_basket
          type: number
          format: currency
          multi_stage: true
          sql: "{CUBE.sales_amount} / NULLIF({CUBE.transactions_without_returns}, 0)"
  ```

  ```javascript title="JavaScript" theme={"dark"}
  view(`retail_analysis`, {
    cubes: [
      {
        join_path: item_location_sales,
        includes: [`sales_amount`]
      },
      {
        join_path: sales_line_item,
        includes: [`transactions_without_returns`]
      },
      {
        join_path: dates,
        includes: [`date`]
      },
      {
        join_path: items,
        includes: [`department`]
      },
      {
        join_path: locations,
        includes: [`region`]
      }
    ],

    measures: {
      aov_basket: {
        type: `number`,
        format: `currency`,
        multi_stage: true,
        sql: `${CUBE.sales_amount} / NULLIF(${CUBE.transactions_without_returns}, 0)`
      }
    }
  })
  ```
</CodeGroup>

The shared dimension cubes sit at root-level join paths, so `date`, `department`
and `region` are common to both facts and can be grouped by.

### 3. Query it

Querying `aov_basket` by `region` aggregates each fact on its own, stitches the
two results on the shared dimension, and takes the division over the joined rows:

```sql theme={"dark"}
-- one aggregating subquery per fact, at the query's grain
SUM(item_location_sales.sales_amount)                GROUP BY region
COUNT(DISTINCT CASE WHEN … THEN transaction_id END)  GROUP BY region
-- final stage, once the two are joined on region
sales_amount / NULLIF(transactions_without_returns, 0)
```

The measure filters travel into the line-item subquery, so the exchange and
channel rules are applied exactly where they were defined.

<Note>
  `multi_stage: true` is what defers the division until both facts have been
  aggregated. Without it, Cube plans the expression as an ordinary calculated
  measure, looks for a single join tree covering both fact cubes, and fails with
  `Can't find join path to join 'locations', 'item_location_sales',
    'sales_line_item'`.
</Note>

[ref-multi-fact-views]: /docs/data-modeling/multi-fact-views

[ref-multi-stage]: /reference/data-modeling/measures#multi_stage

[ref-measure-filters]: /reference/data-modeling/measures#filters

[ref-view-measures]: /reference/data-modeling/view#measures

[link-tesseract]: https://cube.dev/blog/introducing-tesseract