By: Eric Parker
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.