Ranking KPI BEFORE and AFTER filtering

[M_OConnor]

I'm working on a dashboard for KPI.  I've done two images of what I have verses what I want at the bottom!

Let's say I have 5 competing stores. I want to have a value that ranks them when filtered like so:

Store 1:

1/5

Store 2:

2/5 

etc

Basically, if store 1 has a better Sales measure than store 2, it is ranked above it, so that each store has a rank in the grand scheme of things.

This client specifically wants it so that report-level filters are applied however so that no store can see each other's info.

So, if we imagine that I am store 3, and they are the 3rd best performer, they will see:  3/4

The issue here is that when a page level filter is applied, RANKX seems to give each store (aside from the poorest performing one) a rank of 1/5.

For example:

Store 1: 1/5,    Store 2: 1/5  etc etc, but if Store 5 is the worst performer, it is 2/5.

Here's what the DAX looks like:

Rank = CONCATENATE(CONCATENATE(RANX(all('Performance Table'[store name]), [Sales Measure], , DESC), "/", (etc etc (this is for the second number).

Obviously, what is happening is that when the report is filtered, there exists only one store to be ranked against others, so it's becoming '1/5' regardless.

Is there a way to Rank these stores regardless of page level filters?

This means that when the owner of Store 2 looks at their page, they only see:

Store 2: Rank 2/5.

Where it gets worse is that if the client clicks on 'month' or 'year' filter, the rank needs to also change accordingly. This is why I cannot simply write a rank in SQL, as I assume it's too complex in PowerBI to say 'If particular filter is selected, please use this column!' 

Here is an example of what I essentially want. Imagine that 'Revenue' is the [Sales Measure] I want to rank: 

 

This, however, is what I have 

So, how can we rank these stores when a date filter is applied AND so that they can be ranked from 1-4/4 rather than just against themselves?

Thanks!

M

[v-yuezhe-msft]

@M_OConnor,

What fields do you drag into Report Level filters? Do you have revenue values for store C and D in 'Jan' or 'year1' in the following table? I am curious that if you calculate the rank for B(highlighted rank values) based on the revenue values of all the stores(A,B,C,D).


Regards,
Lydia