0 votes
1 view
in BI by (17.6k points)

What I have?

I have two dimensions(state and city) and a measure (glid). I want to find the count of glid since glid is a unique reference no.

I want to arrange my data such that for each state I get only the top 3 or n cities having the highest count of glids, and I want the rest of the cities to be clubbed under a separate category, say "others".

I have done my due research but since I am new to tableau, could not fathom it.

Please be more elaborate in answering. I am attaching an image for further help 

enter image description here

I know it can be done by grouping but since there are too many states and cities, it would be very tedious, can you suggest something?

1 Answer

0 votes
by (47.2k points)

You can use the following steps:

  • Sort 'State' and 'City' in descending order on Field:'Gild' and Aggregation: Count
  • Create calculated field ranking having the definition as index()
  • Drag it to 'Rows'. Right-click this field in here and select 'Edit table calculation'. Select 'Compute Using': Specific Dimensions, Below that check 'State' & 'City' (in order), 'At the level':Deepest, 'Restarting Every': State and other default selections.
  • Create parameter Top 3 - Data type: Integer; Current value: 3; Display format: Automatic; Allowable values: Range; Minimum:3; Maximum:10; Step size:1
  • Right click this parameter and select 'Show parameter control'
  • Create calculated field Top_3_calc

IF  [ranking] <= [Top 3] THEN "Top Values"

ELSE "Others"

END

  •  Drag this calculated field to 'Rows'. Right-click this field in here and select 'Edit table calculation'. Select 'Compute Using': Specific Dimensions, Below that check 'State' & 'City' (in order), 'At the level': Deepest, 'Restarting Every': State and other default selections.

After this, you would require to do few cosmetic changes in order to achieve your specific requirement.

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
0 votes
0 answers
asked Aug 27, 2019 in BI by Vaibhav Ameta (17.6k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...