Forum Discussion
Power Pivot - Calculated Column - How to find the 2nd minimum value based on other column groups
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.
5 Replies
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.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.
