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.