Perhaps you have encountered this challenge before in Tableau.
Imagine you are performing a percentage of total calculation on sales for each state, and are filtering for the top N states (for sales), as shown in the image below.
Your percentage of total adds up to 100%, so at first glance this looks perfect.
Change your top N filter to show the top 5 instead of the top 10 however, and you may notice a problem.
The percentage of totals have changed from last time.
What’s happening is that Tableau is calculating the percentage of total only for the states we have in the view.
Our percentage of total calculation is in fact incorrect – if we are filtering out some states, our percentage of total calculation should not add up to 100%!
How do we fix this? How do we filter for the top N and maintain our percentage of total calculation at the same time?
Researching on the internet, there are various methods, however I particularly like the solution Andy showed me.
First, let’s create a rank for our states, based on their sales.
Let’s convert this calculated field from continuous to discrete, and put the discrete rank into the view.
Here comes the clever part.
Create a calculated field that will tell you whether your state is in the Top N. In the screenshot below, “Show Top N” is the name of the parameter I made to allow the user to change the number N.
This will give you a true/false field. Put the new field into the view. Take “State” off filter. Your view now looks like this.
Now; right click on “False” under the “In Top N” header and select “Hide”.
This will hide any states that aren’t in your top N selection. Crucially though, it’s not excluding them, thus the hidden states are still included in your percentage of total calculation!
Now you should be left with this view (I’ve hidden my column grand total)
If I change my “Top N” from 10 to 5, my percentage of total calculation is preserved.
Credit to Andy for the tip!