RANKX doesn't rank values as expected

[BlueCat]

Hi,

I'm trying to rank my values based on the "Qte_KO" column.  So, the highest value in this column should be number 1, the second number 2, etc. Every operation (OP) gaves a qty of ko values and I want to rank them.

So I tried to use RANKX() to help me doing this.

My values are in a table that is named "operations". There are a lot of others columns with differents values. So I want my ranking to be based only in the two collumns : 'OP_description' which is the name of the operation and 'Qte_KO' which is the quantity of ko values.

Here how I wrote my RANKX() :

column = RANKX(Operations, Operations[Qte_KO])

I created a new column inside my "operations" table with this code.

I used a simple TABLE and I've selected 3 columns : OP_description, Qte_KO and column (the rankx one!)

And here is my result (its a screenshot of the top ten values ordered by Column ASC as you can see, there are more values!) :

The ranking is not correct. The OP number 10 should be higher in the ranking than the OP number 9. 

Because 120'046 is higher than 1'150 right  ? In this case, the operation 10 should be higher than 143 in the ranking because it's higher than 75'000.

So I don't understand why the RANKX() doesn't works here. Is it maybe because he uses others columns for his ranking system, but I want him to be based on those 2 columns only.

I have no filter by the way !

Any help ?

[v-yangliu-msft · Accepted solution]

Hi  @BlueCat ,

Do you perform Sum aggregation on the columns [Qte-KO] and [Column], when the aggregation is performed, the rankings will be added up, resulting in a high Rank.

Before aggregation:

After aggregation:

Here are the steps you can follow：

1. Create measure.

sum =
var _table=
SUMMARIZE('Table','Table'[OP_description],"Qte_Ko1",SUM('Table'[Qte_KO]))
return
SUMX(_table,[Qte_Ko1])

rank =
RANKX(ALL('Table'),[sum],,DESC)

2. Sum the column [Qte_KO]

3. Result:

Please click here for the pbix file

Best Regards,

Liu Yang

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

[johnt75]

try

column = RANKX(ALL(Operations), Operations[Qte_KO])

[BlueCat]

Forgot to mention, that I already tried with an ALL(). No difference in the values.

[johnt75]

Can you share a sample PBIX ?