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

user01295

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?

BlueRooster

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

GROUP BY `Business, `Dept`

 

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

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

user01295

Thanks, this one pointed me in the right direction. I was able to do what I want by repeating some numbers in transform. I think I'm very close to what I had intended and that wouldn't be possible without your help!