Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

 

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]))
 
 
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])

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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).

2 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Anonymous,

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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).