How can I re-categorize any counts < n in Google Data Studio?

user_3907405 0
Viewed 177

I'm creating a dashboard that shows some demographic information for a population. However, in cases where less than 25 individuals show a certain demographic trait, I need to be able to re-categorize that trait as "REDACTED" to protect privacy.

A pseudo-code version might be:

CASE
  WHEN COUNT(gender) < 25 THEN "REDACTED"
  ELSE gender
END

Unfortunately, this mixes aggregation and non-aggregation and isn't workable in a calculated field.

I'm hoping for some suggestions on how to solve preferably without aggregating first in the data source --I'd like to be for my data and blends to exist at the individual grain with aggregation happening within Google Data Studio.

Publicly-Editable Example Google Data Studio report

Data source (INPUT) is the source, also available here.

Expected output:

gender count
female 222
male 223
REDACTED 55
google-data-studio
2 Answers

0) Summary

The below looks at:

  1. Issue: "calculated fields can't mix metrics (aggregated values) and dimensions (non-aggregated values)"
  2. Suggestion: Reaggregating data using a blend

1) Issue

A system error is displayed:

Sorry, calculated fields can't mix metrics (aggregated values) and dimensions (non-aggregated values). Please check the aggregation types of the fields used in this formula. Learn more.

when using the calculated field in the question:

CASE
  WHEN COUNT(gender) < 25 THEN "REDACTED"
  ELSE gender
END

This ("aggregated values") refers to the 2nd line in the CASE, specifically the aggregated field:

COUNT(gender)

2) Suggestion

One way is to use reaggregation with a blend, by performing a self blend (where the data source of both tables are the same) and creating a dedicated COUNT(gender) field (titled gender_count below):

2.1) Blend Fields

Table 1 Table 2
Dimension #1: gender Dimension #1: gender
Dimension #2: person_id
Dimension #3: dept
Metric: Record Count Metric: gender
Aggregation: COUNT
Rename: gender_count
table_1 table_2

2.2) Join Configuration

Join Description Table 1 Table 2
Join Operator Left Outer
Join Condition gender gender
Image join

In summary, the result of the blend will look like:

blend_summary

2.3) Pie Chart

  • Dimension: 
    CASE
  WHEN gender_count < 25 THEN "REDACTED"
  ELSE gender
END
  • Metric: Record Count

Publicly editable Google Data Studio report (embedded Google Sheets data source) and a GIF to elaborate:

gif

user_128925530

Unfortunately Google Data Studio doesn't let you use an aggregate function (SUM, AVG, COUNT) in a CASE statement and return a dimension. However there is a workaround for this. First create a field like this:

field1

CASE 
  WHEN COUNT(gender) < 25 THEN 1
  ELSE 0
END

then create a second one like this

field2

CASE
  WHEN field1 = 1 THEN "REDACTED"
  ELSE gender
END
user_116156000
Related