4 Replies Latest reply on Nov 27, 2012 12:40 AM by Adi Sutanto

How do Top 10 filters filter according to dimension on the axis

Hi All,

I have done an extensive search before I post this question. But I could not find a solution for it.

I want to display Sum(Sales) against locations in a bar chart (chart attached). However, I don't want the total sales, but the sum of top 10 companies' sales number in each location.

However tableau Top 10 Filter on company ID by Sales number will not consider the Location dimension.

How do I tell tableau to get the Top 10 companies for each location instead of Top 10 for the entire data set.

Thanks!

• 1. Re: How do Top 10 filters filter according to dimension on the axis

Hi Marc,

To do this, you'll need to create a nested sort. The following KB should be able to help you get the desired outcome:

http://kb.tableausoftware.com/articles/knowledgebase/nestedsorting

Hope this helps!

-Tracy

• 2. Re: How do Top 10 filters filter according to dimension on the axis

Let me clarify what Marc wants with the Superstore example.

Expected result:

Row 1: Central Region, Sum Sales of Top N Customers bar

Row 2: Central Region, Sum Sales of Non Top N Customers bar

Row 3: East Region, Sum Sales of Top N Customers bar

Row 4: East Region, Sum Sales of Non Top N Customers bar

Row 5: South Region, Sum Sales of Top N Customers bar

Row 6: South Region, Sum Sales of Non Top N Customers bar

...

The chart should not show the Customer name. Instead, it shows the sum of top N and sum of non top N.

We have tried to create the formula but could not yet find the answer.

The attached workbook has "Sales by Region and Customer", but no Sum of Top N yet.

• 3. Re: How do Top 10 filters filter according to dimension on the axis

See the attached. How this works is:

1. Use a calculated field to generate the group for the sort instead of a Set. This is used because it can be materialized in the Tableau data source and can be quite a bit faster when there's high cardinality in the data.

2. Use a Top N calc to create the Top N/Non Top N "bins".

3. Create two calculated fields that use the WINDOW_SUM table calc to sum Sales based on membership in each bin.

4. Add the fields to the view, set the Compute Using to the Region+Customer field created in step 1, and filter for non-Null values.

Jonathan

• 4. Re: How do Top 10 filters filter according to dimension on the axis

Thank you very much for the answer, that is exactly what we need!

Now I have learned this new technique.