3 Replies Latest reply on Sep 10, 2012 7:32 AM by Bryce Cazier

# Limiting to 3 Examples per dimension

Hello all,

I have a quick question.  Below is an example of a table I have.  There are multiple dimensions on the left hand side and two measures to the right.  I would like to limit the businesses (SOY_ID_NR & Candidate_Account_Name) to 3 per each district (DD2) and Segment.  So the idea in this example would be to have the top 3 retail businesses (by revenue or volume) in district 20 and 30 for a total of 6 records.  Is this possible? I have been able to limit the records by revenue or volume but it ignores the district and segment and just takes the top 3 records or the entire data set.  Any help would be great!

Thanks,

Bryce

• ###### 1. Re: Limiting to 3 Examples per dimension

Bryce,

I think you'll need to add your District measure in the Filter as "context," even if you're not wanting to filter it.  Then your Top 3 filter on the businesses will be based on each District.  You may need to do the same with Segment.

HTH,

Scott

1 of 1 people found this helpful
• ###### 2. Re: Limiting to 3 Examples per dimension

Hi Bryce,

This is possible through the use of table calculations.  To do this, try following the steps below:

(1) Create a calculated field where the formula is simply: INDEX()

// This creates the basic ranking which we will use to rank the top 3 and limit based off that

(2) Right click the new calculated field and make it "Discrete."  Apply it somewhere on the view like the Rows to have it display its rank

(3) At this point, it is likely incorrectly ranking our dimensions.  To edit, right click on the field and select "Edit Table Calculations."

(4)  You will need to select the "Advanced..." option under the area where you define the calculation.  This should bring you to a new dialog where you need to move the following dimensions from the left hand side (Available fields) to the right hand side (compute using):

DD2

Segment

SOY_ID_NR

Candidate_Account_Name

(Please be assured that they are in this particular order as well)

(5) At the bottom of the same screen, the Order Along section will determine our method of choosing Top 3.  For this, use either revenue or volume and remember to choose 'descending'.

(6) When you close out of this screen, you should get back to the "Edit Table Calculation" dialog.  For this have it restart for every "Segment."  This will instruct that the top 3 will be for each DD2 and segment combo.

(7) Validate that the ranking is correct on screen.  Right click on the new rank field and create a quick filter where you can define to see only items 1,2, and 3. If done correctly, it should limit the data down so that we see only the top 3 SOY_ID_NR and Candidate_Account_Name for each DD2/Segment combo.

Hope this helps!

Wilson Po

1 of 1 people found this helpful
• ###### 3. Re: Limiting to 3 Examples per dimension

I found both the suggestions above helpful.  Before getting this feedback I was able to achieve the desired result by creating a set.  Hopefully someone else will find this helpful.

http://kb.tableausoftware.com/articles/knowledgebase/finding-top-n-within-category

Thanks,

Bryce C