2 Replies Latest reply on Mar 31, 2016 6:30 AM by Carl Slifer

# Filtering a Measure for the Top Value Only

Hi all

If I have data like the below with Consituencey Name, Name of Candidate and number of votes, I would like to create a calculated field to denote the Candidate in Each Constituency with the Highest number of votes (Or give rank in descending order of highest votes for Each Candidate per Constituency).

This will enable me to do a Viz on just the Election winners

Any help appreciated!

 Barnet G Johnson 1356 G Smith 23548 P Jordan 10134 A Gamble 8444 Cardiff South J Bloggs 25447 S Smith 11444 P Jones 6568 A Griffiths 405 Sunderland A Dalgleish 1545 A McInally 11234 P Gordon 11087
• ###### 1. Re: Filtering a Measure for the Top Value Only

You can try using an Index or Rank function. Personally I prefer to use INDEX() (create a calculated field and enter INDEX() as the formula) and set the Compute Using in your visualization as follows (with your constituency field called Const and Candidate called Cand):

You will also be able to use LOD calculations to select only the candidate with the most votes, or use the INDEX calculated field as a filter to do the same, filtering for number 1.

• ###### 2. Re: Filtering a Measure for the Top Value Only

Howdy Ajay,

Please see the attached workbook. I mocked up your data in Tableau.

1) Drag Area and Candidate to the Rows Shelf.

2) Drag Votes to the Text Shelf of the Marks Card

3) Right Click SUM(Votes) in the Marks Card and choose 'Quick Table Calculation'

4) Choose Rank

This will rank the candidates but will not take into account what area they are in.

5) Right Click the SUM(Votes) again.. This time it will have a small triangle next to it.

Currently this is a table calculation based on the entire table, we want to limit the scope to be based on each area.

6) Choose the option 'Compute Using' and then choose Pane(Down)

Now we are looking at rankings for each individual person based on the area they are in.

7) Right Click this and change it to discrete.

This is just to allow the next step to be possible.

8) Drag this field between area and candidate on the rows shelf

Now you're listing from highest to lowest with respect to each Area

9) Drag another instance of votes onto the text shelf (from the measures pane)

This is just to see the actual values again

10) Hold Ctrl down and left-click and drag your discrete field onto the filters shelf and only choose 1 to keep

Only the number 1 person from each area will persist

Cheers!

Carl Slifer

InterWorks