RANKX not working correctly

[zibster]

Hi,

I am using power pivot and trying to implemant rankx but i dont know why it is not working, I need to rank column Key for each Facility [Fac] at this point all I get is number 1 for all. Please help and Thansk

my current formula is:

=RANKX(ALL(Inv2018T),Inv2018T[FAC],[Key])

and here is the sample of my data

[Michiel · Accepted solution]

RANKX doesn't understand that you mean to take [Key] from the table in which you create this column, and not from the ALL(inv2018T) which is the first argument to the function. Try it with EARLIER([Key]).

Or alternatively, use a variable:

=VAR ThisKey = [Key] RETURN RANKX(ALL(Inv2018T),Inv2018T[FAC],ThisKey)

[zibster]

Hi Michiel, Thanks for advice but when I use the formula you provided I am still getting 1 on all rows.

Thanks

Z

[Michiel]

Oh, I'm being to quick here. Your RANKX function returns the rank of [Key] (when using EARLIER that is) among all values of [Fac]. Looking at your data, [Key] has much larger values than [Fac] and this means that it will probably always come out on top, so 1.

If you need the rank of [Key] among all rows with the same [Fac], you'll need to provide only the rows with the same [Fac] to RANKX. Like this:

= VAR ThisFac = [Fac]
RETURN
RANKX(FILTER(Inv2018T, Inv2018T[Fac] = ThisFac), Inv2018T[Key])

[zibster]

Perfect Thanks for your help Michiel