1 Reply Latest reply on Feb 1, 2013 1:07 PM by Catherine Rivier

    How to count measures above a selected value

    Jackie Klein

      Let say I have a table in tableau that shows me state and sales for each state.

       

      I want to create an action filter so that when I select the sales for a particular state, it tells me the count of states that have sales higher than the selected value, and how many states have sales lower than the selected value. 

       

      Any thoughts on how I can do that?

       

      Thanks,
      Jackie         

        • 1. Re: How to count measures above a selected value
          Catherine Rivier

          Hi Jackie,

          I came up with a possible solution on this, which will work with your example of one state per row in your original data.  See the attached workbook.

           

          Instead of an Action, I created a Parameter, "State Selected", which will select one state at a time for this calculation.

           

          First tab, Calculations Step by Step, I broke down each piece of the calculation into 5 separate calculations:

          1. Calc 1 - Selected State Sales  (the sum of Sales for the Parameter-selected state
          2. Calc 2 - Selected State Sales Across All States (the window sum of Calc 1, so every row will have that selected state's sales as a comparison)
          3. Calc 3 - State Sales Minus Selected State Sales (subtracts Calc 2 from that row's state's sales)
          4. Calc 4 - Count of States w/ Higher Sales (gives a value of 1 if higher than the state, 0 if lower, so we can have a count)
          5. Calc 5 - Total States w/ Higher Sales (another window sum, of all of Calc 4) - this is your count of states with sales higher than your selected state.

           

          Then the second tab, Show Only Current State, I pull it in so it's only showing the current state and the results.  To do this I created calculated field "Calc 6 - Current State", which gives a value of "Current Selected State" to the current state, and "Other States" to all others.  Then pulling that onto the view, I right-clicked on "Other States" and selected "Hide".  (Filters will change the data, so Hide is your option here.)

           

          Is this what you had in mind?

          Catherine