Sum of ranks by several columns

[MarkShu]

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? 

[Nolock]

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)

[MarkShu]

Unfortunately it does not working at all. =(

[Nolock]

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 🙂

[MarkShu]

Rank by Bounce Rate is also very strange. 

[MarkShu]

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

[Nolock]

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

[MarkShu]

Rank by Spendings =

RANKX('А'; SUM('А'[Spendings]);;ASC) 

[Nolock]

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.

[MarkShu]

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

And with calculates it also does not working.  

[Nolock]

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

[MarkShu]

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

[Nolock]

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

Thanks 🙂

[MarkShu]

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