Hows this for you? I've recreated your scenario with Ranking products by state from the superstore dataset...
So I've used the index() function here, as this allows me to determine how this calculation is run, and also order the index.
So for the index() function (I've called this field [Rank]), I've set this up in the following way...using the 'Edit Table Caluclation' -> 'Advanced' option
Please note the Sort part
Please note the Restating every part
I can the create a parameter, so I can control the top N...and set up a filter calculated field [Show Top N by State Filter] as per the below
[Rank]<=[Show Top N]
I then add this to the filter shelf and set to True.
Hope this is what you were after, and makes sense...if the answer to either of these is 'No' let me know and I can add some further explanations/refinements
Show Top N by State.twbx 459.9 KB
Thank you very much for your time and assistance.
Unfortunately I am still relatively new on operating Tableau and I'm not exactly sure of what/how to execute what you ave explained. I have started trying and it is becoming a little clearer but not there just yet.
I will persevere though
No problem...so we've used some fairly advanced features here, in terms of Table Calculations (which take a bit of getting used to...but is well worth it!), and especially using the 'Advanced' option
In brief Tableau has 4 main types of calculation...
Row Level - Performed 'off canvas' against every Row of the data eg. IIF([Sales] > 1000, 'Good', 'Bad')
Aggregated - Aggregates the result based on the Level of Detail in the Viz eg. SUM([Sales])
Table Calculations - Aggregated Calculations (and can be Aggregate on Aggregate), where we can define (by using the Addressing and Partitioning) how the calculation will be run
Level of Detail (LoD) - I won't confuse things here with a full explanation, but are a bit of the above 3!
index() is a Table Calculation. As such by setting it up in the way I have I have asked it to create the index (which is just a numbering) over State and Product, Sort them on Sales, and restart the numbering every State.
It's probably worth you watching the On-demand training videos on Table Calculations (only about 10-20 mins)...which will give you a good introduction to what they are, and then I'd watch this brilliant Video (about an hour) which should give you some more detail on what they are doing (and why)
if you still have any questions on the post following this, please post back and I'll be happy to help
This might not cover your situation but below is an example of a much simpler way to do this by setting your category filter to "Add to Context" .
Starting with a list of sales by country I add a filter to display the top ten countries by sum of sales by placing country on the filters shelf.
But now if I add in market as a filter only the 4 countries from the APAC region that are in the overall top 10 are displayed.
This can be fixed by letting Tableau know that the Market filter should be applied before calculating the top 10. This can be done by right clicking on the market filter and selecting "Add to Context".
The Market filter will now be gray to indicate that it is a in-context filter which runs before all other filters are applied.
Thanks ery much for the feedback! Apologies for the delayed response.
I will give you method a try as didnt have time last week to test these solves and had to do a lot of manual work in excel.
Thanks again for helping!
Thanks very much for all the help on this! Haven had a chance to test all sultion provided but will definitely be try them during the coming week!
Thank you for taking the time to help!