RANKX and FILTER not behaving.

[Stemar_Aubert]

Hi,

I need to rank sales people based on a KPI, and need to filter them. My model has multiple tables. One with the actuals, another with the targets, then one with the location of individuals and then, the individual themselves.

1) The list of sales people includes "misc" individuals, which are simply present to capture all unassigned sales. They must be filtered out from my ranking. For this, I have a column which flags those that must be excluded (0 = in, 1 = out).

2) I must be able to rank everyone world wide, or by region (slicer).

Ideal output looks like this :

1	Joey	50%
2	Rachel	30%
3	Phoebe	29%
4	Chandler	25%
5	Monica	18%
6	Ross	-1%
...	...	...

But then, when I select a region, have the ranking like so :

1	Joey	50%
2	Monica	18%
3	Ross	-1%

It seems like using ALLSELECTED is the solution, but I can't make it work (probably because of the way shadow filter work).

SmartyRank =
CALCULATE (
    RANKX ( ALLSELECTED ( Reps[INDUSTRY REP] ), [PINS vs Targets YTD 19] ),
    FILTER ( Reps, Reps[RepFlag] = 0 )
)

This measure is ranking everybody 1, and doesn't even exclude the RepFlag like I want to.

Using ALL() instead of ALLSELECTED doesn't work either for some reason, and I get a ranking which goes like this when I slice for regions.

SmartyRank = CALCULATE( RANKX ( ALL ( Reps[INDUSTRY REP] ), [PINS vs Targets YTD 19] ),
        FILTER (
            ALL ( Location[REGION] ),
            Location[REGION] = SELECTEDVALUE ( Location[REGION] )))

Also, a last need I have, is I must be able to "cut" the top n results from this table, meaning filtering on the measure itself should not change the ranking. For this reason, ALLSELECTED() doesn't seem to be the solution. ALL() doesn't work because I can't manage to filter out the rows I must exclude in the rank, and it keeps the ranking between regions.

I am aware this is a complex issue, so any insights is greatly appreciated.

[amitchandak]

Can you move you filter FILTER ( Reps, Reps[RepFlag] = 0 ), in the calculation of [PINS vs Targets YTD 19]  and try

[Stemar_Aubert]

Hi @amitchandak 

I wrote the following in order to filter my KPI :

CALCULATE([PINS vs Targets YTD 19],FILTER(Reps,Reps[RepFlag] = 0 ))

But this doesn't work at all. I think I am not doing the FILTER properly. 

Edit :

I wrote this 

CALCULATE([PINS vs Targets YTD 19], Reps[RepFlag] = 0)

And used that instead of the ranking, to see the behavior. This is the result :

So it stops calculating the measure for two rows, which have the flag value 1. This is a good step in the right direction. I can't make the whole ranking formula work with this structure though. Any help ?

[v-chuncz-msft · Accepted solution]

@Stemar_Aubert 

You may check if the post below helps.

https://community.powerbi.com/t5/Desktop/RANKX-excluding-blanks/m-p/396044#M180683