AVG() Beastmode Handling 0s?

NateBI

Hi All,
If have a dataset with duplicate IDs (required) and a metric - let's call it "total apples" against each ID (so also duplicated)

The goal is to get the average of this meric but only considering the trend of distinct IDs overtime.

I've labelled each distinct ID (using row_number over create date partition by id) and using the following beatmode insight:

distinct_total_apples

CASE WHEN row = 1 THEN total_applesELSE 0 END

In the insight if I sum this beatmode it matches another dataset with distinct IDs - perfect.

However when I use the average aggregation (within the insight dropdown) the total_apples drop for many months (aggregated by month). I presume due to the 0s?

The beastmode won't allow ELSE NULL though

What's the move here?

NateBI

Resolved:

Removed the ELSE statement.

distinct_total_apples

CASE WHEN row = 1 THEN total_applesELSE 0 END

NateBI

Resolved:

Removed the ELSE statement.

distinct_total_apples

CASE WHEN row = 1 THEN total_applesELSE 0 END