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