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.
I am getting a response that I cannot mix aggregated and non-aggregated functions within an IF statement. Do you know what this means?
1 of 1 people found this helpful
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.
I have the same issue with an If statement in a table calculation.
My data is as follows:
Month | Year | Metric ID | Metric Value
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"