DAX for Power BI similar to RANK.AVG in Excel

[robibanadera]

Hello! I need help in making a column similar to the results using RANK.AVG in Excel. I have table where there are values that are the same. And when I try to rank them, the results is like this:

Value | Rank

5 | 2

10 | 5

5 | 2

5 | 2

1 | 1

First, I want those values that are the same to have continuous rankings. Then those with same rankings get their continuous ranks averaged. Example below:

Value | Rank | Rank.Avg

5 | 2 | 3

10 | 5 | 5

5 | 3 | 3

5 | 4 | 3

1 | 1 | 1

Hope someone can help me with this! Thank you!

[amitchandak]

@robibanadera , for the rank avg

Try a new column

Rankx(Table, Table[Value],,asc,skip)

For the second column - Rank try like

Rankx(Table, Table[Value]+rand()/100,,asc,skip)

[robibanadera]

Hi, thanks for your reply!

I tried the first formula you gave but it's still skipping ranks. What I would like is to have continuous ranking regardless if the values are the same. So for example:

Value | Rank

1 | 1

5 | 2

5 | 3

5 | 4

7 | 5

[amitchandak]

@robibanadera , That was the second formula. But works better if you create a new column

col 2 = ([Column1]+RAND()/100.0)

Column = RANKX(RankT,[col 2],,ASC,Dense)

[robibanadera]

Oooh nice. This works! My only problem now would be on how to average rankings having the same values. In your screenshot, the rows having same value of 5 has rankings 2, 4, and 3. Can we get a ranking like the computation in Excel function Rank.Avg where the ranking having same values are averaged, in this case it would look like this instead

Value | Rank. Avg
1 | 1

5 | 3

5 | 3

5 | 3

7 | 5

Their ranks would all be 3 because 2+3+4/3 is 3. Rankings were averaged.

[v-yingjl]

Hi @robibanadera ,

You can create a column like this:

Rank.Avg =
VAR t1 =
    ADDCOLUMNS ( 'Table', "New", ROUND ( [Value] + RAND () / 100.0, 4 ) )
VAR t2 =
    ADDCOLUMNS ( t1, "Rank", RANKX ( t1, [New],, ASC, DENSE ) )
RETURN
    AVERAGEX ( FILTER ( t2, [Value] = EARLIER ( 'Table'[Value] ) ), [Rank] )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[robibanadera]

Hi, can you give me the last formula as a measure? 

[v-yingjl · Accepted solution]

Hi @robibanadera ,

The measure would be like this:

Measure = 
VAR t1 =
    ADDCOLUMNS ( ALL('Table'), "New", ROUND ( [Value] + RAND () / 100.0, 4 ) )
VAR t2 =
    ADDCOLUMNS ( t1, "Rank", RANKX ( t1, [New],, ASC, DENSE ) )
RETURN
    AVERAGEX ( FILTER ( t2, [Value] = MAX('Table'[Value]) ), [Rank] )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[robibanadera]

Thank you so much! This worked!