Forum Discussion
Power Pivot - Calculated Column - How to find the 2nd minimum value based on other column groups
Super User
This calculated column leverages the existing calculated column Lowest Bid:
2nd Lowest Bid =
CALCULATE (
MIN ( Table1[Bid Cost] ),
ALLEXCEPT ( Table1, Table1[WAREHOUSE / DIVISION], Table1[ITEM NAME] ),
Table1[Bid Cost] > Table1[Lowest Bid]
)
The logic can be adapted for the % difference measure. Another option is RANKX if you need the 3rd smallest, 4th smallest, etc.
Hello, I know this is an old question, but needed to revert back to it. I tried your suggestion. However, I am getting multiple errors:
The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.
A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.- DataInsights
Would you be able to provide your DAX, and note whether it is a measure or calculated column? If you can paste your sample data as a table, that would help.
This is a Calculated Column. The ultimate goal is to have this data in a Pivot Table (w. Slicers), and be able to see the % difference between the 1st & 2nd lowest bid (By Warehouse, By Time).
I have the Calculated Column for the 1st lowest bid:
=CALCULATE(MIN('Table1'[BID COST]),ALLEXCEPT(Table1,Table1[WAREHOUSE],Table1[ITEM NAME]))and the % difference.
=('Table1'[BID COST]-CALCULATE(MIN('Table1'[BID COST]),ALLEXCEPT(Table1,Table1[WAREHOUSE],Table1[ITEM NAME])))/'Table1'[BID COST]I still need the 2nd Lowest bid. I tried to use your code to find the 2nd lowest bid, but it's throwing an error:
=CALCULATE(MIN('Table1'[BID COST]),ALLEXCEPT(Table1,Table1[WAREHOUSE],Table1[ITEM NAME]),'Table1'[BID COST]>'Table1'[Lowest Bid])The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.
?Would this be easier to do in Power Query before I add the Data to the Date Model?
Sample Table:
SUPPLIER WAREHOUSE UPC ITEM NAME BID COST PROJECTED VOLUME SUPPLIER'S VOLUME LOWEST BID % From Lowest Bid 2nd Lowest Bid % From 2nd Lowest Bid Supplier B Warehouse F 1111111111 Item A $3.94 141,597 141,597 $3.94 0.0% $5.65 -43.4% Supplier H Warehouse F 1111111111 Item A $5.65 141,597 141,597 $3.94 30.3% $5.65 0.0% Supplier D Warehouse F 1111111111 Item A $5.70 141,597 141,597 $3.94 30.9% $5.65 0.9% Supplier A Warehouse F 1111111111 Item A $5.81 141,597 141,597 $3.94 32.2% $5.65 2.8% Supplier G Warehouse F 1111111111 Item A $5.86 141,597 141,597 $3.94 32.8% $5.65 3.6% Supplier F Warehouse F 1111111111 Item A $6.05 141,597 141,000 $3.94 34.9% $5.65 6.6% Supplier E Warehouse F 1111111111 Item A $6.50 141,597 141,597 $3.94 39.4% $5.65 13.1% Supplier C Warehouse F 1111111111 Item A $7.27 141,597 135,000 $3.94 45.8% $5.65 22.3% Supplier A Warehouse F 2222222222 Item B $6.35 80,604 80,604 $6.16 3.0% $6.21 2.2% Supplier B Warehouse F 2222222222 Item B $6.47 80,604 80,604 $6.16 4.8% $6.21 4.0% Supplier C Warehouse F 2222222222 Item B $6.56 80,604 75,000 $6.16 6.1% $6.21 5.3% Supplier D Warehouse F 2222222222 Item B $6.21 80,604 80,604 $6.16 0.8% $6.21 0.0% Supplier E Warehouse F 2222222222 Item B $6.61 80,604 80,604 $6.16 6.8% $6.21 6.1% Supplier F Warehouse F 2222222222 Item B $6.66 80,604 80,000 $6.16 7.5% $6.21 6.8% Supplier G Warehouse F 2222222222 Item B $6.42 80,604 80,604 $6.16 4.0% $6.21 3.3% Supplier H Warehouse F 2222222222 Item B $6.16 80,604 80,604 $6.16 0.0% $6.21 -0.8% Supplier A Warehouse K 1111111111 Item A $5.56 83,469 83,469 $3.82 31.3% $5.56 0.0% Supplier B Warehouse K 1111111111 Item A $3.82 83,469 83,469 $3.82 0.0% $5.56 -45.5% Supplier C Warehouse K 1111111111 Item A $7.21 83,469 78,000 $3.82 47.0% $5.56 22.9% Supplier D Warehouse K 1111111111 Item A $5.63 83,469 83,469 $3.82 32.1% $5.56 1.2% Supplier E Warehouse K 1111111111 Item A $6.56 83,469 83,469 $3.82 41.8% $5.56 15.2% Supplier F Warehouse K 1111111111 Item A $6.01 83,469 83,000 $3.82 36.4% $5.56 7.5% Supplier G Warehouse K 1111111111 Item A $5.72 83,469 83,469 $3.82 33.2% $5.56 2.8% Supplier H Warehouse K 2222222222 Item A $5.60 83,469 83,469 $3.82 31.8% $5.56 0.7% Supplier A Warehouse K 2222222222 Item B $6.18 44,602 44,602 $6.11 1.1% $6.18 0.0% Supplier B Warehouse K 2222222222 Item B $6.35 44,602 44,602 $6.11 3.8% $6.18 2.7% Supplier C Warehouse K 2222222222 Item B $6.50 44,602 44,602 $6.11 6.0% $6.18 4.9% Supplier D Warehouse K 2222222222 Item B $6.22 44,602 44,602 $6.11 1.8% $6.18 0.6% Supplier E Warehouse K 2222222222 Item B $6.67 44,602 44,602 $6.11 8.4% $6.18 7.3% Supplier F Warehouse K 2222222222 Item B $6.61 44,602 44,000 $6.11 7.6% $6.18 6.5% Supplier G Warehouse K 2222222222 Item B $6.28 44,602 44,602 $6.11 2.7% $6.18 1.6% Supplier H Warehouse K 2222222222 Item B $6.11 44,602 44,602 $6.11 0.0% $6.18 -1.1%
