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% - DataInsights
It appears that the provided DAX doesn't work in Power Pivot but it does work in Power BI Desktop (what I'm using). You could explore the Power Query equivalent of this DAX (check the Power Query forum for posts on this topic).