Power Pivot - Calculated Column - How to find the 2nd minimum value based on other column groups

[Cday21]

I'm having trouble creating a Calculate Column in Power Pivot.

I have a table that shows bid results from multiple suppliers for multiple items and divisions.  

Through research I've been able to figure out the DAX formula for the minimum bid (by Division, by Item). 

=CALCULATE(MIN(Table1[Bid Cost]),ALLEXCEPT(Table1,Table1[WAREHOUSE / DIVISION],Table1[ITEM NAME]))

I was also able to figure out the percent difference based on the lowest bid.  

=(Table1[Bid Cost]-CALCULATE(MIN(Table1[Bid Cost]),ALLEXCEPT(Table1,Table1[WAREHOUSE / DIVISION],Table1[ITEM NAME])))/Table1[Bid Cost]

I'm now being asked if I can do the same for the 2nd lowest bid (by Division, by Item) to place in a Pivot Table for when we make negotiation calls. 

I've been looking for a couple days through the forum and google, and cannot seem to find an answer for the 2nd lowest value Calculated Column.

In Excel, the formula would be:

2nd Lowest Bid =SMALL($D$5:$D$13,2)
% From 2nd Lowest Bid =(D5-SMALL($D$5:$D$13,2))/D5

Here is my data sample.  I apologize, I'm trying to figure out how to attached the sample data file, but my OneDrive and GooglDrive is blocked by IT security.

[DataInsights]

@Cday21,

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.

[Cday21]

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]

@Cday21,

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.

[Cday21]

@DataInsights 

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]

@Cday21,

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