4 Replies Latest reply on Nov 26, 2018 3:57 AM by Laurène Ruban

# How to calculate a sum with condition

Hi there,

Thanks in advance for the help and sorry for the beginner question.

I am working on a database looking like that :

ID

RegistrantSectorMaterialTotal amount
1A1Wood123
2A1Plastic123
3A2Wood40
4B1Wood82
5B1Plastic82
6B2Wood20

I want to represent the total amount depending on the sector. In my example, the total amount for the sector "1" is 123+82=205

Problem is in the Tableau sheet I can only select a few aggregation functions, among them"Sum" (which would result in 410 here) and "Max" (which would result in 123 here), which don't give me the wanted answer. How should I do?

For information, I have only 2 registrants (A and B, no C).

I have thought of calculating a sum under condition, for example :

If registrant = A

a = Max(Total amount)

end if

If registrant = B

b = Max(Total amount)

end if

c = a+b

But I don't know if such a code is possible in Tableau.

Thanks a lot,

Laurène

• ###### 1. Re: How to calculate a sum with condition

Hi,

Try this calculation:

Check the attached workbook.

~Tushar

1 of 1 people found this helpful
• ###### 2. Re: How to calculate a sum with condition

Hi, Laurene

Below is the sample output for your reference.

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 3. Re: How to calculate a sum with condition

Based on the table above, I tried to achieve the result you wanted. I have attached the screen snip of how I have achieved it.

My underlying logic to get the total amount for sector 1 as 205 instead of 410, is to use this calculated field (Sector Amount) in the viz

IF SUM([Number of Records]) = COUNTD([Total amount]) THEN SUM([Total amount])

ELSE SUM([Total amount])/COUNTD([Total amount])

END

Hope this helps.

1 of 1 people found this helpful
• ###### 4. Re: How to calculate a sum with condition

Hi Tushar, Zhouyi, Amar,

Thanks a lot for all your quick and useful answers It helped a lot !

Have a nice day,

Laurène