How to aggregate CASE Statement values?
In my dataset, I have three different tests that aggregate the field Forecast
differently. Here's a beast mode I made that works pretty well, except when I'm trying to add them all together:
CASE
WHEN Test
= 1 THEN SUM (Forecast
)
WHEN Test
= 2 THEN SUM (DISTINCT Forecast
)
WHEN Test
= 3 THEN SUM (DISTINCT Forecast
)
END
when I filter by each Test
, it works great. But when I want all of them selected, it doesn't work at all; just defaults to one value.
Example:
Test 1 Forecast: 100
Test 2 Forecast: 350
Test 3 Forecast: 250
If I select all tests, the total should be 700. If I select Tests 1 + 2, it should be 450 and so on. However, it doesn't add these together at all. How can I do this?
Comments

change your logic a bit so that you’re adding your different sums together since you want to add all the sums together based on your filters .
SUM(CASE WHEN test = 1 THEN forecast ELSE 0 END) + SUM(DISTINCT CASE WHEN test = 2 THEN forecast ELSE 0 END) + SUM(DISTINCT CASE WHEN test = 3 THEN forecast ELSE 0 END)
Sorry for the bad formatting. I’m on mobile and on vacation :)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 
awesome, that solves my problem perfectly! Thank you so much Grant!
0 
don't use SUM(Distinct) that's too risky.
Imagine if the forecast value for a set of employees is all 50. SUM distinct would assume there was only one employee with a forecast value of 50.
The better solution is to reshape your data such that you don't have to rely on DISTINCT to deduplicate rows. have a set of rows for the employee forecast, have a second set of rows for the regional forecast and a third set of rows for the state forecast. or however you're rolling up your forecasts.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2
Categories
 10.7K All Categories
 1 APAC User Group
 12 Welcome
 36 Domo News
 9.6K Using Domo
 1.9K Dataflows
 2.4K Card Building
 2.2K Ideas Exchange
 1.2K Connectors
 339 Workbench
 252 Domo Best Practices
 11 Domo Certification
 461 Domo Developer
 47 Domo Everywhere
 101 Apps
 705 New to Domo
 84 Dojo
 Domopalooza
 1.1K 日本支部
 4 道場日本支部へようこそ
 23 お知らせ
 63 Kowaza
 297 仲間に相談
 649 ひらめき共有