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:

Answer 2

You can just apply a filter on the Data Studio graphic, selecting the fruits and colors that you want!

Answer 3

=SUMPRODUCT(QUERY({A2:D}, 
 "select Col4 
  where Col1='Apple' 
    and (Col2='Red' or Col2='Green')"))

but this will work too with that example:

=SUMPRODUCT(QUERY({A2:D}, 
 "select Col4 
  where Col1='Apple'"))