A long title. What do I mean? Well, at work I had a scenario that I was struggling to figure out in Tableau. In Sample – Superstore terms, I wanted to be able to look at each Manufacturer and sort them in descending order by different metrics. I then wanted to be able to limit that selection to a Top N. So maybe I’d want to see the top 10 manufacturers by Sales, but only the top 5 by Profit Ratio.
Fortunately, Andy Kriebel recently uploaded a great video which showed me how to enable users to swap out metrics and conditionally format the output – so Sales can be showed as currency, Profit Ratio as a percentage, Quantity as a Number etc. etc.
So this is a great start. Formatting the numeric output of Measures is important, and this method works a treat and makes absolute sense to me. The challenge I then had was to use this core view and then enable users to filter the number of returned manufacturers based on a parameter. A Top N filter is also a doddle to do in isolation. You can create a basic parameter like this:
And then refer to it in your filter. So if I right-click Manufacturers on Rows and select the Top tab, I can bring the parameter into the filter:
The Sort By Calc is simply:
My theory is that you pick a metric, such as Quantity, and then the combination of the two parameters (Pick a Metric and Top Manufacturers) plus the one calculation (Sort By Calc) will sort and restrict the view to the right metric, and the right number of manufacturers. And it does:
The weird thing is, I swear it didn’t yesterday! I assume I must have messed up with something on the filters somehow, so Manufacturers were getting excluded as I cycled through metrics, and whilst a Top N was being returned, it wasn’t a true Top N due to those exclusions. Somehow it worked when setting things up today, which is annoying as I’d remembered an alternative method of controlling the Top N filter, using INDEX():
A simple boolean. If I include the new calcs on the Rows shelf, this is how the view looks where the Top 5 manufacturers are selected with a parameter, but the view is not filtered to exclude anything:
So you can see what the INDEX() is doing (incrementing a count row by row), and you can also see the evaluation of the second calculation. If I move that second calculation onto the filters shelf instead of Rows (and tick “True”), the filter is applied:
Obviously there are a couple of ways (at least) to achieve the Top N filtering, and the good thing with the use of INDEX() (or RANK()) is that you can use addressing and partitioning to enable this sort of filtering to work even with hierarchical dimensionality, as shown by Tom Brown on this YouTube video:
This is further investigated by Andy Kriebel here: