# Why does drilling into a measure lead to a different value than the value in the aggregated table?

**URL:** <https://community.omni.co/t/why-does-drilling-into-a-measure-lead-to-a-different-value-than-the-value-in-the-aggregated-table/27>\
**Category:** Modeling\
**Created:** [April 2, 2024, 12:16am UTC](https://community.omni.co/t/why-does-drilling-into-a-measure-lead-to-a-different-value-than-the-value-in-the-aggregated-table/27 "2024-04-02T00:16:59Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![conner](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.omni.co/conner/32/11_2.png) [@conner](https://community.omni.co/u/conner)\
**Post date:** [April 2, 2024, 12:16am UTC](https://community.omni.co/t/why-does-drilling-into-a-measure-lead-to-a-different-value-than-the-value-in-the-aggregated-table/27/1 "2024-04-02T00:16:59Z")

</div>

A common workflow when modeling is defining a “filtered measure”, i.e. a measure with a filter. For example:

- **Count of Non-Returned Orders:** Counts the number of orders that _are not returned_
- **Sum of Revenue from non-churned customers:** Calculates total revenue from customers _who have not churned_
- **Average subscription length of customers who have canceled:** Calculates the average subscription length of customers _who have canceled_

### Why drilling may result in a different number of rows than you expected

In SQL, calculating these measures is often done with the help of a `case when` statement to “null”-ify the rows that are excluded from the calculation. For example:

- **Count of Non-Returned Orders:** `sum(case when order_status != 'Returned' then 1 else 0 end)`
- **Sum of Revenue from non-churned customers:** `sum(case when status != 'Churned' then revenue else null end)`
- **Average subscription length of customers who have canceled:** `avg(case when status != 'Active' then subscription_length else null end)`

However, if you drill into one of these measures, the rows that don’t match your filter condition are still in the table — they’re just not getting counted in your calculation. This can lead to more rows than you’re expecting in your drill.

Let’s take the example of **Count of Non-Returned Orders.** If your table looks like this, then your **Count of Non-Returned Orders** should return 2:

| order\_id | status |
| --- | --- |
| 1 | Delivered |
| 2 | Delivered |
| 3 | Returned |

But if you drill into the **Count of Non-Returned Orders** , you’ll still get all 3 rows, including `order_id = 3`, because your SQL isn’t filtering it out. It’s just giving it a value of 0, so that it’s not calculated when you `sum()` the `case when()` statement:

| order\_id | status | case when status != ‘Returned’ then 1 else 0 end |
| --- | --- | --- |
| 1 | Delivered | 1 |
| 2 | Delivered | 1 |
| 3 | Returned | 0 |

### How to fix this

Thankfully, Omni has a feature specifically for these metrics: **Filtered Measures ([docs](https://docs.omni.co/modeling/measures#filters)).** In Omni, you define a measure, and then you can attach Filters to it such that Omni will _filter_ those rows out before calculating the metric.

So, in the above example, you would just define a `count(*)` measure with a filter `order_status != 'Returned'`. Behind the scenes, Omni will filter out any row where `order_status = 'Returned'` before running the `count(*)`.

```auto
  count_california_seniors:
    aggregate_type: count
    filters:
      returned:
         not: null

```

| order\_id | status | counted in count(\*)? |
| --- | --- | --- |
| 1 | Delivered | Yes |
| 2 | Delivered | Yes |
| 3 | Returned | No |