How to filter top customers within certain section in Tableau

Question

I have a Tableau file whose source is from one Excel sheet as shown below:

And it's how it looks in Tableau:

I have a Tableau sheet to show me the top 3 customers in each section. For example, company F, H, J, B, A are in section XXX, I filter section XXX only, sort F, H, J, B, A, select the top 3 customers and hit "keep only". So I have a table of top 3 customers in XXX section. Please see the pics below:

My question is, every month I have an updated Excel sheet with same format and I feed it into Tableau, the sales change every month but I always want top 3 customers in each section. For example, top 3 customers this month in XXX section are F, H, J but next month it might be A,B,H. However since I use "keep only" to filter F, H, J, the top 3 customers can only be F, H, J unless I manually change the filter. Is there anyway I can program Tableau to pick up the top 3 automatically whenever I feed new data to this file? I know "filter top n by" can do it but it can only filter by one variable, like they can only filter by sales instead of sales AND sections. I want to use "by formula" in the pic below to do it but I am not sure how to write the formula.

Answer 1

Have you tried to use the "Top" instead of "Condition" tab? You could also try to use a rank.

Create a calculated field "Rank", put index() into it, make it discrete and put it between your segment and customer pillow. Untick "Show header" and use it as a filter on the filter shelf.

Answer 2

I would use the Quick Table Calculation Rank. To do this create a new worksheet as follows:

1. Drag Section onto Rows
2. Drag Customer onto Rows
3. Drag Sales onto text mark
4. Drag sales onto detail
5. Right click the sales that you have dragged onto detail. Select Quick table calc and then rank.
6. Change this table calc from the detail mark onto the rank mark.
7. Now you can see what it is doing my default. You can see that is will be calculating the rank across all sections. As you mentioned you want the rank per section you just need to adjust the way the quick table calc is working. To do this right click on the measure and select edit table calc. Under Compute using you can change the way the calc is working. Select a couple of different ones to see what is happening. As you have structured the workbook to have Section and then Customer on the Rows shelf you should be able to use pane across then down. This means that it will calculate the rank per pane which in this case is per Section and will calculate down across Customer. 8) You can now drag this measure onto the filters pane and select a range of 1-3 this will filter the view to only show the top 3.

This method will work dynamically even when the data is updated :)