Click to view on Tableau Public – Top and Bottom N Values
Often when analysing data it is important to see your top performing categories such as region, sales agents or products. With traditional reporting the report builder would decide on which values you get to see but in this example I will show you how to allow the user to select the amount themselves. I’ll explain below how to achieve this. I will be using the Order data from the EU Superstore sample data for this example.
Open Tableau and connect to the EU Superstore Order data.
Create a new sheet, double click profit in the measures and then double click country in the dimensions. You will be shown the chart.
This chart shows which countries are profitable and which are not but there is no order or control to what you are seeing. Click the sort from top to bottom button and we can see that the United Kingdom and France are the most profitable countries and that the Netherlands and Sweden are the least. What if we only wanted to see our top and bottom 2 countries?
Create a parameter called “TopX”, set the data type to integer and the current value to 2.
We now need to create the calculation which we will use as the dynamic filter. Create a new calculated field called “Top/BottomXFilter” and enter the following calculation:
IF FIRST() > -[TopX] THEN ‘TopX’ ELSEIF LAST() < [TopX] THEN ‘BottomX’ ELSE ‘Middle‘ END
This calculation will give the top (first) X (in our case 2) and bottom (last) values a label of “TopX” and “BottomX”. Any values that do not meet this criteria will be labelled “Middle”. We will now use these values in a filter to show what we want.
Drag the newly created “Top/BottomXFilter” to the filters shelf, tick ‘Middle’ and then tick exclude. Select OK to accept this filter and you will now see the report below.
You can see that the filter is working and as mentioned before, we can see United Kingdom and France as most profitable and the Netherlands and Sweden as the least profitable.
The final step is to bring the parameter into the view so you can control what you see. Right click on the TopX parameter, select show parameter control and now change the value from 2 to 3. You will notice that Germany and Portugal now show.
That’s it, you no w have a parameter controlled filter which allows the user to see the TopX most, and least profitable countries!