cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Sum of ranks by several columns

Hello!

I want sort my ADs by sum of ranks Spending + Bounce Rate and show up with all another atributs: Source, Ad Campaign, Ad Group, Key Word. I already broke my head trying to figure out how to do it. Can you help me?

13 REPLIES 13
Resident Rockstar

Hi Mark,

if I unterstood you well, you need a rank on an expression which is a sum of ranks on two other columns. If it is so, check my solution.

Rank by Bounce Rate - I think you already have this part.

```Rank by Bounce Rate =
RANKX(TestTable; TestTable[Bounce Rate];;ASC)```

Rank by Spendings - the same as Bounce Rate.

```Rank by Spendings =
RANKX(TestTable; TestTable[Spendings];;ASC)```

And the last one uses the same function RANKX, but as an expression for sorting is a sum of those two ranks.

```Rank by Sum =
RANKX(TestTable; TestTable[Rank by Bounce Rate] + TestTable[Rank by Spendings];;ASC)```

Helper I

Unfortunately it does not working at all. =(

Resident Rockstar

Hi Mark,

please check the screenshot and tell me, if the output is the result you expect.

In the last column you see the value which is used for the final ordering.

Thanks 🙂

Helper I

Rank by Bounce Rate is also very strange.

Helper I

There is a problem: Rank by spendings is 1 everythere.

Resident Rockstar

Hmm, I see, then please send me the formula you use for the calculation of the Rank by spendings.

Helper I
Rank by Spendings =
RANKX('А'; SUM('А'[Spendings]);;ASC)
Resident Rockstar

Aha, please remove the SUM from your formula. You want to rank by the row value of the column, not by a SUM of values in a column.

Helper I

But I want to rank by sum (there are many dates and rows can be)))

And with calculates it also does not working.

Resident Rockstar

And what is the grouping column which you would like to have for summing of groups?

Helper I

For all atributes. Eventually I want see rating from most important to less important ads for ads with the largest spendings and bounce rate.

Resident Rockstar

Could you please write an SQL for that? Maybe after that it'll be clearer what are your expectations.

Thanks 🙂

Helper I

I can't give you SQL, but I have broad table like this  https://dropmefiles.com/S9djd

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.