RANKX problem

[Anonymous]

I have a very simple table to test out the RANKX function shown below

countries	value
Japan	3456234
china	23452345
france	6543
usa	12346
sinagpore	54322

i'm trying to create a new column called rank, and in the column header i used the RANKX code as follow:

Rank = RANKX (ALL(Sheet1[countries]),Sheet1[value])

But the rank column returns 1 for every value. However ,when i use 

Rank = RANKX (ALL(Sheet1),Sheet1[value])

the rank column works as intended. However when i have more data and needs the rank for a specific column i wouldn't be able to do this anymore, so i'm wondering why the first formula doesn't work? Thank you.

[v-xicai]

Hi  @Anonymous ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[Anonymous]

Thank you for your explanation Amy, it was helpful.

[v-xicai · Accepted solution]

Hi @Anonymous ,

The syntax of RANKX function is like this format :RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]]) ,the first argument is Table but not a Filed. You can go through the link https://docs.microsoft.com/zh-cn/dax/rankx-function-dax to learn more about the RANKX .

If you need to rank the value field for every countries categories, then try to create column in DAX below.

Rank= RANKX(FILTER(Sheet1,Sheet1[countries]=EARLIER(Sheet1[countries])),Sheet1[value],,ASC,Dense)

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Zubair_Muhammad]

@Anonymous 

You need a calculated column? Not a measure...right?

Try this as column

Rank =
RANKX (
    ALLSELECTED ( Sheet1[countries] ),
    CALCULATE (
        SUM ( Sheet1[value] ),
        FILTER ( Sheet1, Sheet1[countries] = EARLIER ( Sheet1[countries] ) )
    )
)