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 Parameter-selected 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 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?
StateSelect&Count.twbx.zip 1.1 MB