Rankx with multiple column conditions

[Anonymous]

Hello guys,

I  have a table which has 3 columns which are as follows :

Client Name	Score	Revenue
ABC	9.5	$ 500
XYZ	9.7	$ 600
GPC	9.7	$ 800

Now i need to find top 5 and bottom 5 clients on the basis of both highest revenue and highest score.And wherever there is same score the revenue can help distinguish between them.I tried it through the filter pane but it only allows to filter by one column or measure.
Please help me guys client is just chasing me like crazy on this.

Thanks in advance.

[Anonymous]

Hi again.

I'll give you an example of a construction of a number that will be good for ranking on both columns. Say that your revenues (R) are not greater than 1 000 000. You can get these maximas by querying your table. To make sure you don't have scores like 9.5 you just multiply the numbers by a suitable multiple of 10 - here it'll just be 10 but it could be 100 if a score can be something like 9.55. All your scores must therefore be integers. Based on the above assumptions, you just do this:

S * 1 000 000 + R.

This number, as a calculated column or a measure (depending on what you want to do) will be good for ranking.

Best

D

[Anonymous]

If you want to rank on 2 columns, then it's best to create a measure that will combine both fields somehow in the right way.

Best
D