Tableau - “Cannot mix aggregate and non-aggregate comparisons or results in ‘IF’ expressions.”

If you’ve ever received the error “Cannot mix aggregate and non-aggregate comparisons or results in ‘IF’ expressions" in Tableau I feel your pain. I spent my first several months in Tableau not understanding what that error meant and running into impassable roadblocks when trying to aggregate data as a result.

Let’s say we are working with this data set and we want to know what percent of total sales came from California each year.

31-1.png

If I create a calculation (like below) to answer that question in Tableau I might get an error like this:

31-2.png

This error is due to the placement of the argument SUM() in the numerator. An aggregation function (like SUM) returns a single number for multiple rows of data.

Because we placed the SUM function after the “IF + THEN” statement, Tableau thinks it needs to individually summarize every row of data where the state value is “California”. Visually it looks like this:

31-3.png

There’s no need to sum each of these values individually. What we actually want is to summarize all these individual values together into one larger value like this:

31-4.png

The way that we change that order of operations in Tableau is to apply the SUM function to the entire numerator:

31-5.png

Now we can use this calculated field as we intended to before.

31-6.png

 

Need more help?  Please contact us at freesupport@onenumber.biz.