# Sum of multiple filtered values

Member

Hi,

I am trying to create a % by taking a filtered value in Column A and dividing it by other, multiple filters in Column A. I am able to create the numerator, however, when creating the denominator, I can't get it to register more than one filter. I've tried 'AND' and 'OR' statements, but no avail. See below:

SUM(CASE

WHEN `Material Description` = 'SALT FLUX-57.5/37.5/5'THEN `Quantities Reverse` ELSE 0

END)

/

(SUM(CASE

WHEN `Material Description` = 'CLASS I 3004 BODY STOCK SCRAP' OR '3104/3204' THEN `Quantities Reverse` ELSE 0) +
(CASE WHEN `Material Description` = '3XXX RSI(DROSS)' THEN `Quantities Reverse` ELSE 0) +
(CASE WHEN `Material Description` = '3003 CLEAR/BARE' THEN `Quantities Reverse` ELSE 0))

END

As stated, the denominator only registers the first case ('CLASS I 3004 BODY STOCK SCRAP' OR '3104/3204' in this case). Any advice on how to group the denominator filters?

Also, I tried creating dynamic segments for each of these and did, but don't know if I can reference dynamic segments in formulas. I tried but got not result.

## Best Answers

• Coach
Answer ✓

@ConstelliumShawn It looks like your first CASE statement was the only one grouped inside the SUM. Rather than using separate CASE statements for each value of Material Description, you can structure your denominator to use a single CASE statement with each condition separated by WHEN… THEN… like this:

```SUM(CASE
WHEN `Material Description` = 'CLASS I 3004 BODY STOCK SCRAP' THEN `Quantities Reverse`
WHEN `Material Description` = '3104/3204' THEN `Quantities Reverse`
WHEN `Material Description` = '3XXX RSI(DROSS)' THEN `Quantities Reverse`
WHEN `Material Description` = '3003 CLEAR/BARE' THEN `Quantities Reverse`
ELSE 0
END)
```

Alternatively, you can use the IN operator to make a single condition like this:

```SUM(CASE
WHEN Material Description in
('CLASS I 3004 BODY STOCK SCRAP','3104/3204','3XXX RSI(DROSS)','3003 CLEAR/BARE')
THEN Quantities Reverse
ELSE 0
END)
```

• Coach
Answer ✓

I agree with Michelle's suggestion. I'd also point out that in your original syntax, you are missing multiple END statements. Every time you use CASE, you need to have a matching END. You have 2 CASEs with no ENDs.

**Check out my Domo Tips & Tricks Videos

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

## Answers

• Coach
Answer ✓

@ConstelliumShawn It looks like your first CASE statement was the only one grouped inside the SUM. Rather than using separate CASE statements for each value of Material Description, you can structure your denominator to use a single CASE statement with each condition separated by WHEN… THEN… like this:

```SUM(CASE
WHEN `Material Description` = 'CLASS I 3004 BODY STOCK SCRAP' THEN `Quantities Reverse`
WHEN `Material Description` = '3104/3204' THEN `Quantities Reverse`
WHEN `Material Description` = '3XXX RSI(DROSS)' THEN `Quantities Reverse`
WHEN `Material Description` = '3003 CLEAR/BARE' THEN `Quantities Reverse`
ELSE 0
END)
```

Alternatively, you can use the IN operator to make a single condition like this:

```SUM(CASE
WHEN Material Description in
('CLASS I 3004 BODY STOCK SCRAP','3104/3204','3XXX RSI(DROSS)','3003 CLEAR/BARE')
THEN Quantities Reverse
ELSE 0
END)
```

• Coach
Answer ✓

I agree with Michelle's suggestion. I'd also point out that in your original syntax, you are missing multiple END statements. Every time you use CASE, you need to have a matching END. You have 2 CASEs with no ENDs.

**Check out my Domo Tips & Tricks Videos

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