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

user01295
user01295 Member
in Archive

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

 

BusinessDeptMetricsValues
ClothingKidsCost21
ClothingKidsSales80
ClothingWomenCost35
ClothingWomenSales50
FoodCookedCost65
FoodCookedSales62
FoodFreshCost15
FoodFreshSales60

 

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

Metricssum of ValuesMyCol
Cost1360
Sales252136

 

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?

Comments

  • BlueRooster
    BlueRooster Domo Employee

    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:

     

    BusinessDeptCostSales
    ClothingKids2180
    ClothingWomen3550
    FoodCooked6562
    FoodFresh1560

     

    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
    user01295 Member

    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!

Categories