9 Replies Latest reply on Aug 2, 2018 2:49 AM by Paulo Dantas

# Top 5 Cities in top 5 States

Hi guys,

Wondering if you could help.

Using the Sample - Superstore data from Tableau. How would i create a table showing the top 5 cities in the top 5 states based on sales.

For example, below are the top 5 states in terms of sales. Now i would like the top 5 cities in those states (based on sales)

 California \$457,688 New York \$310,876 Texas \$170,188 Washington \$138,641 Pennsylvania \$116,512

Thanks!

• ###### 1. Re: Top 5 Cities in top 5 States

Hi Ryan,

You can do that using Index- calculated field. Follow the steps for the same

1. Put State and City on Rows shelf

2. Sales on the Text/ Marks card

3. Create a calculated field -> Index ()

4. Put Index in the rows shelf -> and in Edit Table Calculation-> ref screenshot

5. Drag Index on to the filters and select first 5

6. Drag States on the filter and select top 5

Hope this helps,

Soham

2 of 2 people found this helpful
• ###### 2. Re: Top 5 Cities in top 5 States

Hi Ryan,

You can do this by having the index() function and then having a top 5 on the state filter.

Put your State and City on the Rows, your State on the filters and filter by top 5 by sales descending, then put your index with a table calculation off of city, sum of sales descending and limit to 5.

Andrew

3 of 3 people found this helpful
• ###### 3. Re: Top 5 Cities in top 5 States

Andrew

• ###### 4. Re: Top 5 Cities in top 5 States

Hah! no worries Andrew Bickert ! same solution as yours!!

• ###### 5. Re: Top 5 Cities in top 5 States

HI Ryan,

Check the detailed step by step process with using Rank function

1) Drag State to rows and sales to text

2) Drag State to filter and Select top 5 and Click ok

3) Now drag City next to state

4) Create a calculated field like below

5) Change the calculated field to discrete

6) Drag Rank Field in between State and City

7) Click on Rank Edit Table calculation

8) Change edit table calculation like below

9) Drag the Rank by pressing CTRL from rows section to filter and select 1 to 5

10) you will get the view like below showing top 5 cities in top 5 states

11)Click on Rank and uncheck show Header

FINAL VIEW:

Hope this detailed explanation helps

Kindly mark  this answer as correct or helpful so that it will help others

BR,

NB

2 of 2 people found this helpful
• ###### 6. Re: Top 5 Cities in top 5 States

Thanks so much mate! really appreciate you helping me out. was googling it for ages! Thanks!

• ###### 7. Re: Top 5 Cities in top 5 States

Thanks so much!

• ###### 8. Re: Top 5 Cities in top 5 States

Hi Ryan,

You can refer the below link :

1 of 1 people found this helpful