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

Modeling
1

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). 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(*).

  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