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 for 5 years. 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.

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.


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


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:


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:


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


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