Multiple columns as a pie chart on Data Studio

Question

I'm trying to make a pie chart using three different columns from my sheets dataset (table 9) – caretaker issues, child issues, and housing issues. I'm trying to visualize the percentage/total number for each column in a pie chart, but Data Studio only allows one metric.

Here's how the columns are laid out:

I've tried grouping them together using these CASE statements, but it only shows numbers as their dimension.

CASE
WHEN Housing_Issues IS NOT NULL THEN Housing_Issues
WHEN Child_Issues IS NOT NULL THEN Child_Issues
WHEN Caretaker_Issues IS NOT NULL THEN Caretaker_Issues
END

Here's the link to the dashboard, and the pie chart I'm trying to create is on page 3. Any suggestion? Thanks.

Answer 1

Here's a hybrid approach that first transforms the data in Google Sheets in a process referred to as unpivoting and then creates a Dimension field in Google Data Studio:

1) Google Sheets

Create a new sheet and copy-paste the formula below in cell A1, where Table 9_AK represents the sheet name (as shown in the DataSet sheet); it also replaces line breaks and _ with a (space) in the original headings:

=QUERY(
{ArrayFormula(SPLIT(
{FLATTEN('Table 9_AK'!A2:A&"|"&TEXT('Table 9_AK'!B2:B,"DD MMM YYYY")&"|"&TRIM(REGEXREPLACE('Table 9_AK'!C1:AA1,"(_|"&CHAR(10)&")"," "))&"|"&TRIM('Table 9_AK'!C2:AA))},"|",))},"
SELECT *
WHERE Col1 is NOT Null
LABEL Col1 'Agency', Col2 'Date', Col3 'Breakdown Dimension', Col4 'Metric'
",0)

_{NOTE: Data can continue to be added in the original sheet (Table 9_AK), and it will automatically be transformed in the DataSet sheet}

2) Google Data Studio

• Chart: Pie Chart
• Dimension: Dimension

CASE
  WHEN REGEXP_MATCH(Breakdown Dimension, ".*(Caretaker).*") THEN "Caretaker Issues"
  WHEN REGEXP_MATCH(Breakdown Dimension, ".*(Alleged Neglect|Housing|Abandonment|TPR|Place).*") THEN "Housing Issues"
  WHEN REGEXP_MATCH(Breakdown Dimension, ".*(Child|Alleged|Prenatal).*") THEN "Child Issues"
  ELSE "Other"
END

• Metric: Metric

Editable Google Data Studio Report (Embedded Google Sheets Data Source) and a GIF to elaborate: