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

    Filtering a Measure for the Top Value Only

    Ajay Gajree

      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!

       

      BarnetG Johnson1356
      G Smith23548
      P Jordan10134
      A Gamble8444
      Cardiff SouthJ Bloggs25447
      S Smith11444
      P Jones6568
      A Griffiths405
      SunderlandA Dalgleish1545
      A McInally11234
      P Gordon11087
        • 1. Re: Filtering a Measure for the Top Value Only
          Andrew Watson

          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
            Carl Slifer

            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