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.

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