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

Ranking Bid Submission

Hi all,

 

I need to to rank bid submissions by unique lane ID. Essentially I have many suppliers that are going to be bidding on a list of lanes. Each lane has a unique ID or Number. I need to rank their submission against the total for the ID they bidded against. Typically in excel I do it using a sumproduct formula. I need to figure out how I can rank the bid against the total for the Lane ID in power bi.

 

Column G represents the Lane Number or ID and column N represents the price that was submitted.

 

=SUMPRODUCT((G2=$G:$G)*(N2>$N:$N))+1

Capture.PNG

 

I have tried the following expression but only get everything back as 1

 

Column = RANKX(ALL('Carrier Master'[Lane Number]), SUMX(RELATEDTABLE('Carrier Master'), [40' DRY rate (No Bunker) - Port to Door]))
 
Capture.PNG
 
Update I feel like I am getting closer.
 
Tried using this formula and it works for all numbers but is not able to compare the rank to just the unique lane number.
 
Column 2 = RANK.EQ('Carrier Master'[40' DRY rate (No Bunker) - Port to Door], 'Carrier Master'[40' DRY rate (No Bunker) - Port to Door])

 

2 ACCEPTED SOLUTIONS
v-chuncz-msft
Community Support
Community Support

@Anonymous,

 

Wrap the <Expression> in RANKX within a CALCULATE statement.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

I think i got it.

 

TestAll-Inclusive FEU rate (40' Dry, No Bunker) - Port to Port = if('Carrier Master'[Carrier Name] = "Test Supplier",'Carrier Master'[All-Inclusive FEU rate (40' Dry, No Bunker) - Port to Port] +1,'Carrier Master'[All-Inclusive FEU rate (40' Dry, No Bunker) - Port to Port])

 

 

above is the corrected formula but below is the help i got.

 

 

I found that the value (of All-Inclusive FEU rate (40' Dry, No Bunker) - Port to Port) was same for each supplier within a Lane number. That was the reason you were getting 1 Rank. So 2 things we have to do add one measure and modify the rankx formula

  1. Create a measure column which will be Total on 'Carrier Master'[All-Inclusive FEU rate (40' Dry, No Bunker) - Port to Port]) .

Say for example

TotalAll-Inclusive FEU rate (40' Dry, No Bunker) - Port to Port  = sum('Carrier Master'[All-Inclusive FEU rate (40' Dry, No Bunker) - Port to Port])

  1. Remember Rankx is an iterator and we want to run only on suppliers.

Rank Suplier with in Lane# = if(isblank('Carrier Master'[TotalAll-Inclusive FEU rate (40' Dry, No Bunker) - Port to Port]),BLANK(),   RANKX(Filter(All('Carrier Master'[Carrier Name]), Not(ISBLANK([TotalAll-Inclusive FEU rate (40' Dry, No Bunker) - Port to Port]))), [TotalAll-Inclusive FEU rate (40' Dry, No Bunker) - Port to Port],,ASC))

 

In the attached file I added TestAll-Inclusive FEU rate (40' Dry, No Bunker) - Port to Port (for testing purpose as the values were same for each suppliers), Second thing I added TotalRate Measure (similar to #1) and third thing I did is added Rank Suplier with in Lane#(Similar to #2).

View solution in original post

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@Anonymous,

 

Wrap the <Expression> in RANKX within a CALCULATE statement.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

I think i got it.

 

TestAll-Inclusive FEU rate (40' Dry, No Bunker) - Port to Port = if('Carrier Master'[Carrier Name] = "Test Supplier",'Carrier Master'[All-Inclusive FEU rate (40' Dry, No Bunker) - Port to Port] +1,'Carrier Master'[All-Inclusive FEU rate (40' Dry, No Bunker) - Port to Port])

 

 

above is the corrected formula but below is the help i got.

 

 

I found that the value (of All-Inclusive FEU rate (40' Dry, No Bunker) - Port to Port) was same for each supplier within a Lane number. That was the reason you were getting 1 Rank. So 2 things we have to do add one measure and modify the rankx formula

  1. Create a measure column which will be Total on 'Carrier Master'[All-Inclusive FEU rate (40' Dry, No Bunker) - Port to Port]) .

Say for example

TotalAll-Inclusive FEU rate (40' Dry, No Bunker) - Port to Port  = sum('Carrier Master'[All-Inclusive FEU rate (40' Dry, No Bunker) - Port to Port])

  1. Remember Rankx is an iterator and we want to run only on suppliers.

Rank Suplier with in Lane# = if(isblank('Carrier Master'[TotalAll-Inclusive FEU rate (40' Dry, No Bunker) - Port to Port]),BLANK(),   RANKX(Filter(All('Carrier Master'[Carrier Name]), Not(ISBLANK([TotalAll-Inclusive FEU rate (40' Dry, No Bunker) - Port to Port]))), [TotalAll-Inclusive FEU rate (40' Dry, No Bunker) - Port to Port],,ASC))

 

In the attached file I added TestAll-Inclusive FEU rate (40' Dry, No Bunker) - Port to Port (for testing purpose as the values were same for each suppliers), Second thing I added TotalRate Measure (similar to #1) and third thing I did is added Rank Suplier with in Lane#(Similar to #2).

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.