dbt-labs / dbt-labs/metricflow
[Feature] Mixed-Granularity Derived/Ratio Metrics (Ignore Dimensions in Denominator)
- Dominant language
- Python
- Stars
- 1.8k
- Forks
- 202
- Avg merge
- 1d 8h
- Merged PRs (30d)
- 14
Description
### Is this your first time submitting a feature request?
- [x] I have read the [expectations for open source contributors](https://docs.getdbt.com/docs/contributing/oss-expectations)
- [x] I have searched the existing issues, and I could not find an existing issue for this feature
- [x] I am requesting a straightforward extension of existing metricflow functionality, rather than a Big Idea better suited to a discussion
### Describe the feature
#### Summary
We have a common “rate over total population” pattern where the **numerator** should respect the breakdown dimension, but the **denominator** should remain the **overall population** (not split/filtered by that dimension).
Today, MetricFlow / dbt Semantic Layer applies the same `GROUP BY` dimensions to both sides of a derived/ratio metric, which incorrectly segments the denominator and produces misleading rates.
This is extremely common (contact rate, conversion rate, attachment rate). Tools like Tableau (LOD/FIXED) and Looker support this natively.
---
#### Problem / Current Behavior
When querying a derived/ratio metric grouped by a categorical dimension, MetricFlow requires that dimension to exist on **all** sub-metrics and applies it to the `GROUP BY` for each sub-metric.
As a result, the denominator is incorrectly grouped by the breakdown dimension.
---
#### Example Use Cases
1) **CS Contact Rate**
- Numerator: `total_cs_tickets` (should group by taxonomy)
- Denominator: `total_customers` (should be total population, not grouped by taxonomy)
- Query: `cs_contact_rate` grouped by `taxonomy_l1`
2) **Product Attachment Rate**
- “What % of our overall customer base has product XY (or product category/family)?”
- Numerator respects product dimension
- Denominator stays “all customers”, not broken down by product attributes
---
#### Proposed Feature
Allow a derived/ratio metric to specify that **a referenced sub-metric should ignore selected dimensions in its GROUP BY**.
Example interface (illustrative):
```yaml
- name: cs_contact_rate
type: derived
label: CS Contact Rate
type_params:
expr: "total_cs_tickets * 1.0 / NULLIF(total_customers, 0)"
metrics:
- name: total_cs_tickets
- name: total_customers
exclude_dimensions:
- taxonomy_l1
```
When querying cs_contact_rate grouped by taxonomy_*, the denominator (total_customers) should be computed at a higher grain (excluding those dims), then broadcast/joined back to the numerator grain for the final division.
---
### Describe alternatives you've considered
I have tried below but does not work
- `non_additive_dimension`: only works for time dims, not categorical dims
- Defining denominator from a separate semantic model (without the dimension):
- MetricFlow still requires all `GROUP BY` dimensions to exist on both sub-metrics
- Conversion metrics: similar constraint; does not solve the need to exclude group-bys on a sub-metric
### Who will this benefit?
#### Why this Matters
This enables a broad class of “share of total / rate over population” metrics in the semantic layer without:
- custom SQL models,
- materialized intermediate tables, or
- BI-tool-specific LOD/FIXED logic.
### Are you interested in contributing this feature?
_No response_
### Anything else?
_No response_
Contributor guide
Assessment
This issue has not been assessed yet.