# Do a "Sumif" using CASE that's independent of other CASEs

Member

How do I create a beast mode calculation to "sumif" without letting other CASE affecting?

Apologies for the vague description, I'm unsure on how to word this. This is my example dataset

 Business Dept Metrics Values Clothing Kids Cost 21 Clothing Kids Sales 80 Clothing Women Cost 35 Clothing Women Sales 50 Food Cooked Cost 65 Food Cooked Sales 62 Food Fresh Cost 15 Food Fresh Sales 60

I want to create a table card that looks like this, where MyCol is a calculated column populated by the sum of Cost when Metrics = Sales

 Metrics sum of Values MyCol Cost 136 0 Sales 252 136

Using

(CASE when `Metrics`='Sales' then SUM(case when `Metrics`='Cost' then `Values` else 0 end) else 0 end)

doesn't work because by the time the sum calculation is done, all Cost rows have already been eliminated by the first CASE.

Could anyone help with this?

• Coach

So to do what you're looking for, you'll need to alter your source data a bit. You can do this in a data transform, but basically you'll need to seperate out your Metrics into a Cost column and Sales column and then just have the corresponding values below. So you would have the following:

 Business Dept Cost Sales Clothing Kids 21 80 Clothing Women 35 50 Food Cooked 65 62 Food Fresh 15 60

If you're doing this in a MySQL transform, you can use the following:

SELECT `Business`,  `Dept`, SUM(CASE WHEN `Metrics` = 'Cost' THEN `Values` END) AS 'Cost',

SUM(CASE WHEN `Metrics` = 'Sales' THEN `Values` END) AS 'Sales'

FROM SourceTable

This way you break your Cost and Sales into seperate columns and can then show them together.

Hopefully this will get you on the right track. Let me know if you have questions from this point.

Sincerely,

ValiantSpur