Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Rankx with multiple column conditions

Hello guys,

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

 

Client NameScoreRevenue
ABC9.5$ 500
XYZ9.7$ 600
GPC9.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.

 

2 REPLIES 2
Anonymous
Not applicable

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors