Hi Mary. I'm struggling to come up with a way to force the rank to be calculated before the filter is applied. Although it is not the exact solution you desire, your best bet may be to not use Top 3 Regions as a filter on the dashboard, but instead as a highlight action. Go to Dashboard>Actions>Add Action>Highlight then click OK (all the default settings should work just fine). This way, selecting one of the regions still differentiates it from the rest, but does not mess up the displayed Ranks. Let me know if this is a sufficient solution.
In my real-world application, there are potentially over a hundred "regions" or what in my data would correspond to regions in this example. We want to isolate the chosen item. Thanks for your reply.
1 of 1 people found this helpful
Jonathan Drummey posted an outstanding solution to this same problem:
I'm not completely sure what issues there may be in implementing it for your
particular dataset, or how it may perform given the quantity of the values in
Just to try it out, I extracted just the Region and Sales data from your example,
added a Key column of just 1s, and cross-joined all the regions to each other.
I followed the steps that Jonathan outlined and got it working in the attached workbook.
You may also want to upvote this idea of Ranking with Level of Detail
I marked your answer as Helping Me but I can't use it in my workbook because I am also using filters. I thought of creating a separate data source just for ranking but with filters it's too complicated. I upvoted the Ranking with Level of Detail idea. Thanks for your input.
Sorry that wasn't working out.
The community may be able to come up with some other solutions,
but just wanted to get expansion on a few points, if I may:
-what is the nature of your filters: do they change the underlying Sales, for example, such that the ranking changes?
-what is your type of datasource?
The filters would change the underlying Sales. The rankings would change.
The datasource is a pre-aggregated table in a data warehouse (SQL Server, but not Analysis Services). It is aggregated by quarter.
Thanks for the information.
Will explore and ponder.