Looker Studio: How to apply filters from one data source without duplicating metrics in a 1:N relationship

Looker Q&A Looker Studio
1

I’m facing an aggregation issue in Looker Studio involving two data sources with different granularities.

I have:

  • An events data source, where each user (id_user) appears multiple times. All filters (e.g. first media source, plan, date, etc.) come from this table.

  • A user-level metrics data source, where each id_user appears only once (e.g. LTV or vl_transaction_cost).

The goal is to apply filters from the events data source and correctly calculate a user-level average metric (e.g. average LTV), considering only users that match those filters.

The issue is that when blending these data sources in Looker Studio, the user-level metric gets duplicated due to the 1:N relationship, even when using aggregations such as AVG, MAX, or MIN.

I’ve already tried:

  • Blending the data sources using id_user as the join key

  • Creating an intermediate metric using MAX(vl_transaction_cost) and then applying AVG

  • Applying filters only on the events data source

However, the results are still incorrect depending on the filters applied.

I’d like to understand what is the correct approach in Looker Studio to apply filters from one data source while keeping correct aggregation on another when dealing with a 1:N relationship.

2

do you need 2 data sources ? i think you can directly create aggregated views from the events data source

3

Thanks for explaining the issue so clearly.
I’m new to Looker Studio, but I’ve faced similar duplication problems when blending 1:N data sources.
I’m following this thread to learn the correct approach.:blush:

4

In Looker Studio, the duplication of metrics in a 1:N (one-to-many) relationship occurs because the join happens before aggregation. For every event row found in your “Events” table, Looker Studio joins the single user metric from your “User-level” table, leading to inflated sums and incorrect averages.

To solve this and apply filters from the events table without duplicating metrics, use one of the following methods:

1. The “Re-aggregation” Blend Method

This approach forces Looker Studio to collapse the “many” side (events) into a single row per user before it joins or averages the final metric.

Step 1: Create a new Blend.

Step 2 (Left Table): Use your Events Data Source.

Dimensions: id_user and any fields you need for filtering (e.g., first media source, plan).

Metrics: Leave blank or include a dummy metric like Record Count.

Step 3 (Right Table): Use your User-level Metrics Data Source.

Dimensions: id_user.

Metrics: Your target metric (e.g., LTV or vl_transaction_cost).

Step 4 (Join): Configure a Left Outer Join.

Join Condition: id_user.

Result: By including id_user as a dimension in both tables, the blend creates one row per user that already includes the event-level dimensions. When you place a chart on your report using this blend, any metric from the user-level table will now correctly represent a single user value.

2. Using Cross-Filtering (No Blend Required)

If you do not need to combine dimensions in a single table, you can avoid blending altogether by using Looker Studio’s natural cross-filtering behavior.

Setup: Place a Filter Control or a table from the Events data source on your page.

Requirement: Ensure both data sources have an identically named field (e.g., id_user or a shared Date field).

Action: When you select a value in the “Events” filter, Looker Studio will filter the “User-level” charts as well, provided they share the same join key dimension. This calculates the AVG across the unique users that remain after the filter is applied.

3. Calculated Field workaround (SUM_DISTINCT)

If you must use a single combined data source and want to avoid the “many” side duplication:

Use the MAX or MIN function within a chart to see the value for a single user, but note that this will not work for an overall average.

For an overall average across filtered users, use the formula: SUM(User Metric) / COUNT_DISTINCT(id_user).