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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
el_lloydie
New Member

Ranking help please

Hi guys

 

I'm very new to this so I could well be running before I can walk.

 

I essentially have a table that details suppliers with a monthly performance score.

 

Can anyone help me with a formula to show the top 10 suppliers by score (so 100% top) and then sorted by their number of jobs (high to low) please?

 

I've tried numerous methods from Googling but none have yielded the desired result.

 

The table is called Supplier Master and the column headings are Supplier, Overall Score and Jobs Raised.

 

Many thanks in advance,

Gareth

1 ACCEPTED SOLUTION

Hi EL_lloydie,

 

According to your description, you need to background sort them by score and get the top 10 suppliers, and display them order by jobs raised, right?

 

I have tested it on my local environment, the steps below is for you reference.

 

  1. Click Edit Queries > Edit Querie
  2. Click arrow in Overall Score column > Sort Descending
    Untitled2.png
  3. Click Keep Rows > Keep Top Rows, type 10 in the Number of rows textbox.
    Untitled.png
  4. Click Close & Apply button
  5. Create a calculated column using the DAX below.
    Rank = RANKX(Supplier,Supplier[Job Raised])
    Capture.PNG
  6. Then display the data on the visuals.
    Capture1.PNG

Regards,

Charlie Liao

View solution in original post

5 REPLIES 5
achinm45
Advocate IV
Advocate IV

There is RANKX function in DAX. You can use that to Rank.

 

ADD a new Column in your table . Lets Say we call it as Rank

Rank = RANKX(ALL(Table),MonthlyPerformanceScore,Number of Jobs)

 

I hope this works

BR

Achin

Thanks Achin

 

It sort of works, only I can't understand the rankings!

 

Below is my data (admittedly, sorted by score (high to low).

 

I need it to background sort them by score (high to low) and then by jobs raised (so that it goes high to low). Then the supplier at the top would be ranked 1, the next supplier would be 2 and so on.

Data.png

 

 

Thanks,

Gareth

Hi EL_lloydie,

 

According to your description, you need to background sort them by score and get the top 10 suppliers, and display them order by jobs raised, right?

 

I have tested it on my local environment, the steps below is for you reference.

 

  1. Click Edit Queries > Edit Querie
  2. Click arrow in Overall Score column > Sort Descending
    Untitled2.png
  3. Click Keep Rows > Keep Top Rows, type 10 in the Number of rows textbox.
    Untitled.png
  4. Click Close & Apply button
  5. Create a calculated column using the DAX below.
    Rank = RANKX(Supplier,Supplier[Job Raised])
    Capture.PNG
  6. Then display the data on the visuals.
    Capture1.PNG

Regards,

Charlie Liao

waltheed
Impactful Individual
Impactful Individual

The result of DAX queries is never sorted. You have to do that in your client tool.

The sorting in your table screenshot may look strange because you do not show the decimals?

Also note the last argument of the RANKX function, which defines how to deal with ties.

 

The approach is good.

Add a column with rank, call it Ranking:

=RANKX(ALL('Supplier Master');[Overall Score];;DESC;Dense)

 

Do your query (not sure where you are creating the table, but you could do it like this, e.g. with the DAX Studio add-in for Excel):

evaluate

topn(10,'Supplier Master',[Ranking],ASC)

order by [Ranking] ASC, [Jobs Raised] DESC

 

Cheers, Edgar Walther
ITsmart BI and Analytics consultant
Sean
Community Champion
Community Champion

Yes it would be nice if RANKX could actually use another [Measure] to break the ties!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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