When creating formula in Tableau, one might encounter one of these errors,
'Cannot mix aggregate and non-aggregate arguments with this function' or
'Cannot mix aggregate and non-aggregate comparisons or results in 'if' expression'
The reason is one cannot mix an aggregate argument such as AVG(), SUM(), COUNT() with a non-aggregate dimension.
Tableau has published a KB article that provides resolutions for this error. The article has different resolutions for different calculation scenarios.
Let's go through an example to demonstrate how to resolve this error based on the sample data set and business requirement.
The sample data set has 2 dimensions 'Table', 'Access', and 1 measure 'Sigma'.
Step 1: Average the sigma values for each table, which results in an aggregate value.
@Avg Sigma
AVG([Sigma])
Step 2: Calculate if absolute value of @Avg Sigma < 1 then True else False.
@Avg Sigma Boolean
if ABS([Avg Sigma]) < 1 then True else False end
Step 3: Compare if Avg Sigma Boolean = True or Access = True then assign 'Pass' else 'Fail'. Note that this formula mixed an aggregate and non-aggregate comparisons.
@Pass/Fail
if {FIXED [Table]:[Avg Sigma Boolean]} or [Access] = True then 'Pass' else 'Fail' end
Here level of detail calculation is used to bring Avg Sigma Boolean to a row level calculation and to de-aggregate Avg Sigma Boolean. This method is listed under option 4 in the KB article.
Step 4: Count how many tables are assigned 'Pass', which is the final result.
@Pass Tables
COUNTD(if [Pass/Fail]='Pass' then [Table] end)
One might be tempted to make this 'Pass/Fail' formula work by wrapping [Access] with ATTR function in order to make both arguments aggregate.
@Pass/Fail (kaputt)
if [Avg Sigma Boolean] or ATTR([Access]) then 'Pass' else 'Fail' end
However, when you count how many tables are assigned 'Pass' by making another aggregation, you'll encounter the error 'Cannot mix aggregate and non-aggregate comparisons or results in 'if' expression '.
@Pass Tables (won't work)
COUNTD(if [Pass/Fail (kaputt)]='Pass' then [Table] end)
