Forum Discussion

Cday21's avatar
Cday21
Frequent Visitor
3 years ago

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 t...
calculated column
Excel
Need Help
Power Pivot
DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
3 years ago

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's avatar
Cday21
Frequent Visitor
2 years ago

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.