topic Re: DAX for Power BI similar to RANK.AVG in Excel in DAX Commands and Tips

DAX for Power BI similar to RANK.AVG in Excel

robibanadera — Mon, 18 Oct 2021 07:45:52 GMT

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

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!

Re: DAX for Power BI similar to RANK.AVG in Excel

amitchandak — Mon, 18 Oct 2021 07:49:37 GMT

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)

Re: DAX for Power BI similar to RANK.AVG in Excel

robibanadera — Mon, 18 Oct 2021 07:57:54 GMT

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

Re: DAX for Power BI similar to RANK.AVG in Excel

amitchandak — Mon, 18 Oct 2021 08:11:41 GMT

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)

Re: DAX for Power BI similar to RANK.AVG in Excel

robibanadera — Tue, 19 Oct 2021 01:40:22 GMT

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

7 | 5

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

Re: DAX for Power BI similar to RANK.AVG in Excel

v-yingjl — Thu, 21 Oct 2021 02:00:02 GMT

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.

Re: DAX for Power BI similar to RANK.AVG in Excel

robibanadera — Thu, 28 Oct 2021 06:01:50 GMT

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

Re: DAX for Power BI similar to RANK.AVG in Excel

v-yingjl — Thu, 28 Oct 2021 06:35:08 GMT

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.

Re: DAX for Power BI similar to RANK.AVG in Excel

robibanadera — Thu, 28 Oct 2021 09:31:11 GMT

Thank you so much! This worked!