Cannot Mix Aggregate and Non-Aggregate Functions

Updated: Sep 2

Have you ever received the error in Tableau? This can be kind of frustrating if you need to normalize any of your values for a dashboard.


What exactly is this error? It occurs when you try to mix an aggregated argument such as SUM([Sales]) with a non-aggregated function such as Discount or Category.


You have to make them all aggregates or make them non-aggregates / row level variables.


So you can't have [Profit] / SUM ([Sales]),

You will need to use SUM ([Profit]) / SUM ([Sales]) instead.


Or


You can't have [Profit] / SUM ([Sales])

But you can have [Profit] / [Sales].


Or


You can't use [Profit] / SUM ([Sales])

You have to use [Profit] / { fixed: SUM ([Sales]) }


The examples above were taken from this Tableau article here. If you prefer a YouTube video, Zen Master Chris Love has a video here. They both review the Superstore Data set and it is very easy to follow along.


I wanted to use my own use case in this blog article.


In the example below I used this data set from Data World capturing the number of Deaths in Game of Thrones. I added a Rowcount of 1 for every row and I added the following calculation to my Excel sheet with Alteryx to condense the number of Houses.


Revised House Name

If Contains([Allegiance],"Free Folk") then "Free Folk"

ELSEIF Contains([Allegiance],"House Baratheon") then "House Baratheon"

ELSEIF Contains([Allegiance],"House Bolton") then "House Bolton"

ELSEIF Contains([Allegiance],"House Frey") then "House Frey"

ELSEIF Contains([Allegiance],"House Greyjoy") then "House Greyjoy"

ELSEIF Contains([Allegiance],"House Lannister") then "House Lannister"

ELSEIF Contains([Allegiance],"House Stark") then "House Stark"

ELSEIF Contains([Allegiance],"House Targaryen") then "House Targaryen"

ELSEIF Contains([Allegiance],"Night's Watch") then "Night's Watch"

ELSEIF Contains([Allegiance],"None") then "None"

ELSEIF Contains([Allegiance],"House Arryn") then "Other Houses"

ELSEIF Contains([Allegiance],"House Baelish") then "Other Houses"

ELSEIF Contains([Allegiance],"House Clegane") then "Other Houses"

ELSEIF Contains([Allegiance],"House Hollard") then "Other Houses"

ELSEIF Contains([Allegiance],"House Martell") then "Other Houses"

ELSEIF Contains([Allegiance],"House Moore") then "Other Houses"

ELSEIF Contains([Allegiance],"House Mormont") then "Other Houses"

ELSEIF Contains([Allegiance],"House Tully") then "Other Houses"

ELSEIF Contains([Allegiance],"House Tyrell") then "Other Houses"

ELSEIF Contains([Allegiance],"House Umber") then "Other Houses"

ELSE "Other Allegiances"


I used the below calculated fields to capture the counts of deaths in the seasons;


House

If Contains([Allegiance],"House") then 1

Else 0

END


Not House

If Not Contains([Allegiance],"House") then 1

Else 0

END


With these calculations I created the below summary to check my calculations.

I first tried to get a unique House count by using a Fixed calculation.


I noticed this calculation wouldn't work. It was displaying a total count of 2245 which is the total number of rows.


I also wanted to create a Normalized calculation in order to create Map layers.


I then revised my calculation to have an If Then statement inside a SUM() formula.


This calculation worked for my data but it would error for my Normalized calculation. I then had to change the Normalized calculation by duplicating the Count format above and replacing the SUM with a MAX formula.


House Frey MAX

MAX(IF [Revised House Name] ="House Frey" Then [House] END)


I then changed my normalized calculation to the formula below;


The dashboard I am working on for this blog is located here. I am still updating it at this time to add additional map layers.



155 views0 comments