Sum if using Case When

Cintia
Cintia Member
edited April 2023 in Beast Mode

How can I write this formula in an effective way?


(CASE WHEN `Data`= 'Goal' THEN SUM(`Total`) ELSE 0 END)

(CASE WHEN `Data`= 'Pipeline' THEN SUM (`Total`) ELSE 0 END)

(CASE When `Data`= 'in P&L' THEN SUM (`Total`) ELSE 0 END)


I need to create for every name, but it only works with the first one that I created

Best Answer

  • MarkSnodgrass
    Answer ✓

    Do you want to sum them separately and then add them together? Or do you want them as 3 separate calculations? If you want them as 3 separate calculations, then you would create 3 different beast modes, one for each.

    It may be easiest if you explain what it is you are trying to achieve. What are you trying to display?

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • Try this:

    SUM(CASE WHEN `Data` in ('Goal','Pipeline','in P&L') THEN `Total` ELSE 0 END)
    

    This should get the total you want.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • I don't want to sum all of them, I want to sum them separately

  • MarkSnodgrass
    Answer ✓

    Do you want to sum them separately and then add them together? Or do you want them as 3 separate calculations? If you want them as 3 separate calculations, then you would create 3 different beast modes, one for each.

    It may be easiest if you explain what it is you are trying to achieve. What are you trying to display?

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Yes I need to create 3 beast modes separate, but it only worked with the first column

  • Try moving the SUM to the outside of your CASE statement instead of being inside. I would also double-check your data to make sure you are matching exactly, such as case-sensitivity and extra spaces.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.