5 Replies Latest reply on Jun 27, 2018 3:56 AM by Samane Ramezani

# How to write an IF statement with a calculation inside?

Hi -

I am trying to write an IF statement in Tableau that will calculate the average of values if it meets the IF criteria. For example, I would like a formula that answers the following statement:

If an analysis = "Analysis 1", then average the values that pertain to Analysis 1.

Any help?

Thank you,

Kate

• ###### 1. Re: How to write an IF statement with a calculation inside?

Kate,

It's a difficult question without knowing what your data looks like or how it is structured.  It could be as simple as:

IF analysis = "Analysis 1" THEN AVG(value) END

If that isn't it, then please post some sample data.

Regards,

Joshua

• ###### 2. Re: How to write an IF statement with a calculation inside?

I am getting a response that I cannot mix aggregated and non-aggregated functions within an IF statement. Do you know what this means?

Thank you,

Kate

• ###### 3. Re: How to write an IF statement with a calculation inside?

Kate,

I should have anticipated that!  So, there are three types of calculations in Tableau (but you really only need to worry about two of them right now).

1. Row Level -- calculation is performed for each row of data (e.g. IF Sales < 500 THEN "Small" ELSE "Large" END //means that for each sale if it is less than 500 dollars then it is small)

2. Aggregate Level -- calculation is performed at an aggregate level based on the level of detail in (e.g. IF SUM(Sales) < 500 THEN "Small" ELSE "Large" //calculated based on sum of sales for whatever level of detail is present in your view, say department or category)

3. Table Calculations -- whereas 1 and 2 are done at the data source, these calculations are performed on the data table returned to the Tableau Data Engine.  They are very powerful, but can get fairly complicated.

So, the error message is basically saying that you can't mix cases 1 and 2 (which I did in my example without even thinking).

So, you'll need to decide exactly what logic you want based on how your view is constructed.  Let's say that you are building a text table with a single row for each analysis type.  In that case you wouldn't even need the calculation, because you could just place value in the view and change the Measure from SUM to Average.

It could potentially get more complicated if you are trying to do something else.  It may help to have a packaged workbook with sample data to get down to the specifics.

Regards,

Joshua

1 of 1 people found this helpful
• ###### 4. Re: How to write an IF statement with a calculation inside?

I have the same issue with an If statement in a table calculation.

My data is as follows:

Month | Year | Metric ID | Metric Value

11|2012|1|2000

11|2012|2|200

What I want to do is make a table calculation:

IIF([Metric ID] = 1,[Metric Value],0)

This seems pretty simple but I get the error:  "The Formula must be a valid Table Calcuation"

Any ideas?