There are a few ways of doing this. I think the easiest is with Sets. Sets also make it easy to show the bottom 10 on the same graph. (See Re: How to create Top 5 and Bottom 5 values with respect to a Measure in Bar graph in same worksheet)
1. Create a set [Top N by sales]
Right-click on Customer > Create Set > Top > By Field Sum Sales and top 10 (Or you can create a parameter N and make it adjustable.)
2. Create a calculated field [Display Name] =
IF [Top N by sales] THEN [Product] ELSE "Other" END
3. Create a calculated field [Display rank] for sorting =
IF [Display Name] == "Other" THEN "Last" END
4. Add [Display Name] and [Display Rank] to view. Right-click on [Display Rank], select Discrete and move it to the left of Display Name. Right-click on Display Rank and uncheck show header.
5. Go to Analysis > Show Totals > Show Column Grand Totals.
6. You can remove the line above the Other, if you want, by formatting the view, click on the Grid icon > Row > Row Divider > move Level slider to the left.
Note that for (2) to work, I think you'll need a proper database connection or an extract. The Microsoft Jet connection to Excel, Access, Txt doesn't allow this (I think).
Another trick if you want to have a "Show all" it might be easiest to duplicate the view and then on a dashboard switch between the two worksheets. See Is it possible to toggle back and forth between using a dimension with some grouping applied, and the full dimension with nothing grouped?. You can of course also just set N to a large value, if you've used a parameter for the Top N set.
You can find other solutions by searching for TopN. ...
I am getting some error messages in trying out the above. I successfully created the Top 10 by sales set.
The next step:
IF [Top 10 by sales] THEN [Product] ELSE "Other" END
is giving me the error message that "Top 10 by sales" is refering to an undeflined field.
Also, what should [Product] be? Would it be [Customers] in this case?
Sorry, I should have made this clearer. The set [Top 10 by sales] will only be available for calculated fields if you have an extract (or database connection). Right-click on the data connection > Extract Data. ...
Offhand I'm not sure how else to get an "Other" using sets. But if you don't want to use extracts (why not?), then you could use this approach here: Creating "Other" Bin of smallest measures.
And, yes, [Product] should be [Customers] in your case. ... Sorry about that.
One other thought.
The second method using table calculations has another advantage. If you add additional dimensions to your view---say customer segment---then you'll see the Top N customers in each segment. With the set approach you'll see the overall top N customers repeated in each segment, even if they are not a Top N customer for a particular segment ...
I did create an extract and successfully created the Top 10 by sales set. (it appears in a section "Sets" below Dimensions and Measures But when trying to create a new calculated field, the set name does not appear as an option in the list of fields.
could it be that my version of Tableau is 7.0?
The functionality that Jim is describing is only available in Tableau 8. Sets can't be used in calculated fields in v7.
Here's a table calculation version that does work in version 7:
Hi, thanks for the lead. I could not open the example provided
in the link provided above (it gives me a message "the workbook will be
upgraded when it is saved. The upgraded file cannot be read by earlier versions
of the application”.
Nonetheless, I tried following the steps provided by Jim in the link
above (“creating “other” bin of smaller measures). I got only so far, where it
provided the correct ranking but did not summarize the customers ranked below
the Top 10 in one line called ‘other’. I am attaching my worksheet in case that
makes it clearer.
Thanks for the help.
Top10 example.twb.zip 32.6 KB
I'm sorry you're having trouble with this task --- I feel I may have steered you in the wrong direction initially.
For us to review your Tableau workbook, you'll need to save it as a "Packaged Workbook" with a twbx extension. It looks like you already have an extract created for the workbook, so just select Save As > Packaged Workbook. The file should have a twbx extension.
Swee, the message you received was just a warning message, you can go ahead and open the workbook, then save it in version 7.0.
I am having trouble posting a reply.
To make life easier, I upgraded to Tableau 8 and followed your initial instructions using the sets. I am however still having trouble with the Display Rank field. I have attached the file. Appreciate the help.
i can't seem to post a message with the packaged workbook file attached .... ! It does not go through. is there a something that I am missing? I know that this sounds very basic and I should have trouble doing this.
I followed those steps but nothing happens when I hit send. The message does not load. I have waited for up to to 5 mins. Wondering if something is wrong with the system and not allowing it to load with an attachment. The file size is about 67kb so it is not too big.