Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@Anonymous,
Wrap the <Expression> in RANKX within a CALCULATE statement.
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
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])
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).
@Anonymous,
Wrap the <Expression> in RANKX within a CALCULATE statement.
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
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])
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).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
97 | |
75 | |
63 | |
62 |
User | Count |
---|---|
139 | |
104 | |
104 | |
80 | |
66 |