0 votes
1 view
in BI by (15.1k 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 (41.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

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...