Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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).
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |