Forum Discussion
Helper IIIRANKX not working correctly
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
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)
4 Replies
- Michiel
Resolver III
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)
zibsterHi 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])