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

By: Eric Parker

Pro Headshot.jpg

Eric Parker lives in Seattle and has been teaching Tableau and Alteryx since 2014. He's helped thousands of students solve their most pressing problems. If you have a question, feel free to reach out to him directly via email.

Need help with your Tableau calculations? Sign up for an office hour to work with Eric! Want to dive deep into Tableau? Check out our upcoming Tableau classes!

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

Creating a Dynamic Date Field in Tableau

How to Combine Multiple Fields in a Tableau Join

0