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