
1. Re: How to count measures above a selected value
Catherine Rivier Feb 1, 2013 1:07 PM (in response to Jackie Klein)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:
 Calc 1  Selected State Sales (the sum of Sales for the Parameterselected state
 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)
 Calc 3  State Sales Minus Selected State Sales (subtracts Calc 2 from that row's state's sales)
 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)
 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 rightclicked 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

StateSelect&Count.twbx.zip 1.1 MB