1 Reply Latest reply on Feb 12, 2013 12:31 PM by Jim Wahl

# Creating "Other" Bin of smallest measures

Hello all,

Suppose one had a dimension which had about 8 to 10 variables and wanted to aggregate all of the 4 (say) smallest measures (sum) into a new bin called "Other", how would one do that?  I would like my viz to only show individual values for the largest 5 variables and aggregate all the others into a single value of "Other".

My intent is a version of the solution posted on http://kb.tableausoftware.com/articles/knowledgebase/creating-variable-sized-bins, except that the variables are unspecified and hence dynamically generated.

LKL

• ###### 1. Re: Creating "Other" Bin of smallest measures

I'm not quite sure what you mean when you say dimensions, variables, measures, but I think what you want to do is create an other bin for smallest dimension fields?

For example, if you have a product dimension with 10 product names, show the top five by sales and group the remaining five in "other products"?

The attached workbook does this and is based on Joe Mako's excellent description here (http://community.tableau.com/message/168033#168033).

Here is the process I used to create the view

1. Create two parameters:

1.1. Choose top # products -- integer -- parameterizing this will make it easier to test

1.2. Show all products -- boolean -- expands the other category on demand

2. Create calculated fields:

2.1. In top n = INDEX() <= [Choose top # products]

2.2. Products (top) =

IF [In top n] THEN ATTR([Product])

ELSE STR(SIZE() - [Choose top # products]) + " others" END

You're duplicating the product dimension here and creating the "other category"

2.3 Display values (sales) =

THEN SUM([Sales])

ELSEIF INDEX() == [Choose top # products]+1

THEN WINDOW_SUM(SUM([Sales]),0,LAST())

END

The WINDOW_xxx part of this function is what sums the others.

2.3 Display rank = IF [In top n] THEN INDEX() ELSE [Choose top # products]+1 END

This will push the "others" to the bottom.

3. Putting it all together

3.1. Right click on parameters and select "show parameter control"

3.2. Put product on the level of detail shelf.

3.3. Put Display values (sales) on the columns shelf.

3.4. Put Display rank on the rows shelf.

3.5. Put Products (top) on the rows shelf.

3.6. Select Compute using "Product" for both the pills added in 3.3, 3.5 and 3.6

3.7. On the Product pill, select Sort > Descending , Sales SUM

3.8. Add "In top n" to color shelf. Change the colors. Select Sort > Manual with True first

4. Cleanup

4.1. On Display Rank pill, unselect "Show header" to hide the rank value.

Again, check out Joe's post for more detail and options, including multiple levels of dimensions, ...

Jim

1 of 1 people found this helpful