Recently, I was working with a dataset on top football league scorers and ran into an interesting challenge. I wanted to analyse which players scored the most goals in a given year.
While reviewing the complete list of football players and their goal counts for a particular year, I noticed something intriguing. When sorting the data in descending order for 2016, the top five scorers were L. Messi, P. Aubameyang, R. Lewandowski, H. Kane, and E. Dzeko.
However, when I applied a filter to display only the top N scorers, the list of the top five goal scorers changed unexpectedly. P. Aubameyang, H. Kane, and E. Dzeko disappeared from the list, even though they scored more goals than L. Suárez, C. Ronaldo, and C. Immobile in 2016. This left me wondering—what caused these players to drop off the list?
Order of Operations in Tableau
The issue stems from Tableau's Order of Operations, which is the sequence Tableau follows when performing various actions/operations. In other words, the order of operations determines how Tableau processes and filters the data, which can significantly impact the results you see.
The Tableau order of operations includes the following from top to bottom:
More on Tableau Order of Operations in Tableau's Documentation
In my case, I applied filters to display the top five football scorers based on the sum of goals and the year.
The issue occurs because I am using both a Top N calculation and a dimensional filter (Year). In the order of operations, Top N calculations sit above dimensional filters, therefore the top N happens before the year filter.
What Tableau does is first identify the top five football scorers across the entire dataset, then apply the year filter. In other words, Tableau finds the players with the most goals across all years, and only afterwards filters the data to show their goal counts for the specified year. This means Tableau is not finding the top five scorers for a specific year but rather showing how the top overall scorers performed in that year.
No matter which year we filter for, the same five names remain on the list. This happens because Tableau first identifies the all-time top scorers across the entire dataset before applying the year filter.
As a result, even if other players scored more goals in a specific year, they don’t appear in the top five because Tableau prioritises the overall top scorers.
To ensure Tableau considers the year filter first, we need to move the filter above the Top N calculations by setting it as a context filter.
Context filters
A context filter is a filter that is applied before any other filter in a worksheet. In our case, because context filters sit at a higher level in Tableau’s order of operations than Top N calculations, adding the year filter as a context filter ensures it is applied first. This way, Tableau will filter the data by the specified year before performing the Top N calculation.
This will allow us to see the top football scorers with the most goals in a specific year.
