Rank by 2 metrics

[slribeiro]

Greetings, I have one problem and I was hoping you could help me.

I have the following table:

This table is suposed to be ranked by growth but in the case of having equal values, ranked by Value.

Also I need that the rank values instead of appearing "2-3", appear 2 or 3 separately 

So the final table should be something like:

Carlos     1     300     50

Maria     2     2000     30

John     3     700     30

Romi     4     1400     20

I can't make anything like rank(sum(Value*Growth)) because growth is a string and sometimes has values such as 'NEW'

Thanks in advance and best regars.

[sunny_talwar]

Try this:

=Num(Rank(RangeSum(Sum(Growth), Sum(Value)/Max(TOTAL Aggr(Sum(Value), Name))), 4, 1))

[slribeiro]

That would work if I have all integers, but I need to have strings like you can see in the attachment.

But that solution is really really close.

[slribeiro]

So the final order should be alphabetically from Z->A and then decreasing the numbers

[sunny_talwar]

Would you be able to provide information regarding what the expected output needs to look like in this case?

[slribeiro]

Yes of course...

For the example attached, that I think is more clear for you to understand, I should have:

1    Andres    400    NEW

2    Xavier    300    NEW

3    Django    200    NEW

4    Carlos    300    50

5    Maria    2000    30

6    John    700    30

7    Romi    1400    20

The criteria is...

If it's NEW clients stay ordered in the first places by Value, then we order by Growth, then Value in case of being equal.

[sunny_talwar]

Try this:

=Num(Rank(RangeSum(If(sum(Growth) = 0, RangeSum(Max(TOTAL Aggr(Sum(Growth), Name)), 1), Sum(Growth)), Sum(Value)/Max(TOTAL Aggr(Sum(Value), Name))), 4, 1))