Tableau Filter on field which contains MAX of another field

Question

I have a table in Tableau that contains football teams, their top goal scorers and the number of goals players scored. I would like to filter the table to show the team which has the player who has scored the most goals.

For example, if my table has Team A and Team B, and Team B has the player which has scored the most goals out of every player (in all the teams), then I would like the filter to include only Team B (but show every player in Team B).

Answer 1

This is a good use case for a top filter.

1. Place Team on the filter shelf.

2. When defining the filter, choose the Top tab.

3. Select By field, Top 1, Number of Goals and Max

This tells Tableau to determine the maximum value for the [Number of Goals] field for each Team, and then filter to only include the Team with the top value.

In SQL, this typically leads to a HAVING clause.

The only downside is if two teams tie for the top position, I believe you will only see one of them in that case.

If that case is important to you, you can get a similar effect using a table calc to rank teams by their max [Number of Goals], setting the tie breaking rule of your choice for the quick table calc, and then using that calc on the filter shelf to only show teams with the top rank. This will show multiple teams if they are tied for top rank.

The table calc approach is more flexible but can be less efficient, especially for large data sets, since the data is fetched from the data source to Tableau for the ranking calculation, and then only some of it is displayed. (Table calc come very late in the processing pipeline) The top filter approach performs the calculations and filters at the data source, and only sends the filtered results back to the Tableau client.