SUMIF & AVERAGEIF, just a CASE WHEN with 0s or nulls
In this older article, we wrote everything you needed to about CASE WHEN.
Well, almost everything.
Here, we're going one step beyond into the world of calculated fields. So, let's say you've got stocks data, and want to create 2 columns to split your investments between Google and Apple. You could take advantage of a good old SUMIF to isolate, but it doesn't work this way in Looker Studio.
Instead, use a CASE WHEN statement to replicate SUMIF. In your table: Add metric, create field, and then paste the formula below:
WHEN Stock='GOOG' THEN Invested
And that's pretty much it ! What we've done here is to tell Looker Studio to replace values with 0 when the condition is not met, so it doesn't sum. We kept the auto aggregation to SUM, but we could also have enclosed the formula with a SUM(...) to force it.
Very similar for AVERAGEIF: just change the auto aggregation and you're almost ok? NO, BEWARE: if you are to to do an AVERAGEIF, replace 0 with null. You don't want to average (valueA valueB 0 valueC 0) / 5; You just want to average (valueA valueB valueC) /3, and to do so use nulls instead of 0s.
If you get this trick, SUMIF and AVERAGEIF will have no secret for you.