How can I calculate total amount using SUM () and CASE () together in Google Data Studio?

Question

An example of the Google Sheets file that is connected with Google Data Studio:

Fruit	Color	Period	Amount
Apple	Green	201901	3
Apple	Green	201902	5
Apple	Red	201812	5
Apple	Red	201903	4
Grape	Red	201902	4
Grape	Purple	201902	6
Grape	Purple	201903	1

For eg., I want to calculate the total amount for the fruit of green and red apples.

So the formula should be something like this:

SUM Amount WHERE Fruit = "Apple" AND Color = "Green" OR Color = "Red"

In this example, the total amount should be 17

I tried to use this formula in the calculated field, but unfortunately, it does not work:

SUM (Amount) WHERE (CASE WHEN Fruit = "Apple" AND Color = "Green" OR Color = "Red" ) then 1 else 0 END )

I get a syntax error. How can I solve this?

Answer 1

It can be achieved using the CASE statement below (the required aggregation can be selected, e.g. Average, Sum, Count, etc):

CASE
  WHEN Fruit = "Apple" AND (Color = "Green" OR Color = "Red") THEN Amount
  ELSE NULL
END

Google Data Studio Report to demonstrate, as well as a GIF showing the process below using a Chart-level formula: