CODEWITHSUNDEEP
casedashboardlooker-studio
smoothiegirl97
smoothiegirl97

Reputation: 19

Multiple columns as a pie chart on Data Studio

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: enter image description here

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.

enter image description here

Upvotes: 0

Views: 4272

Answers (1)

Nimantha
Nimantha

Reputation: 6461

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

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:

6

Upvotes: 3

Related Questions