are you trying to sort on the total for a State across all of the categories ore on a selected category as well?
Another question - are you trying to limit the view with Index in the sorts such that when you choose ascending and let's say 5 values to show - that the bottom five States will show for the total across all categories or just to sort the view so that now 1 is at the bottom and 5 is at the top- so the same states as the Desc view will appear in the Asc view?
Sorting by the total is what I assume and you nearly have it with the current version. See the attached updated version with the sort controlling if the view shows the bottom 5 states for each region or top 5 states for each region.
I hope this gets you going.
This is great, I've learned to place the calculated field [sort test] over the Details mark.
Yes, this is what I was looking for: rank by the total across all categories per state, and the top 5 need not be the same as the bottom 5, just the top or bottom states per region. I guess I should've used a quantity rather than a percentage in the example to make things more clear about wanting to rank by totals across all categories.
Question: Would you know how to also show the Grand Total per row? This would help the user understand that ranking is by the total across categories. Because we've used Index, if I select Analysis > Totals > Show Row Grand Totals, the error message says, "Cannot Turn on Grand Totals for discrete measures". Is there some way of going around this by perhaps using a parameter instead of Index?