Forum Discussion
Get Value Associated with the Minimum Number per Group
I have the following table:
| Foreign Key | Company Name | Bid | Cheapest Bid |
| 1234 | Fruit&Veg Ltd. | 12,000 | 4,000 |
| 1234 | Plastic Ltd. | 4,000 | 4,000 |
| 1234 | Technology Plc. | 6,000 | 4,000 |
| 5678 | Paper Ltd. | 15,000 | 10,000 |
| 5678 | Chewing Gum Plc. | 25,000 | 10,000 |
| 5678 | Fire Protection Plc. | 10,000 | 10,000 |
I am simply trying to create a new calculated column which will extract the Company Name associated with the cheapest bid. The column highlighted in bold below is what I am trying to achieve.
| Foreign Key | Company Name | Bid | Cheapest Bid | Cheapest Bidder |
| 1234 | Fruit&Veg Ltd. | 12,000 | 4,000 | Plastic Ltd. |
| 1234 | Plastic Ltd. | 4,000 | 4,000 | Plastic Ltd. |
| 1234 | Technology Plc. | 6,000 | 4,000 | Plastic Ltd. |
| 5678 | Paper Ltd. | 15,000 | 10,000 | Fire Protection Plc. |
| 5678 | Chewing Gum Plc. | 25,000 | 10,000 | Fire Protection Plc. |
| 5678 | Fire Protection Plc. | 10,000 | 10,000 | Fire Protection Plc. |
The DAX function seems to me as though it would be a simple calculated column, but I have not managed to find the correct solution. My apologies as I am still relatively new to Power BI. Looking forward to your answers - any help will be appreciated. Thanks in advance!
My bad. Working too fast. Here you go.
Lowest Bidder =
VAR cheapest = Bids[Cheapest Bid]
RETURN
CALCULATE (
MIN ( Bids[Company Name] ),
ALLEXCEPT ( Bids, Bids[Foreign Key] ),
Bids[Bid] = cheapest
)Pat
4 Replies
- mahoneypat
Microsoft Employee
My bad. Working too fast. Here you go.
Lowest Bidder =
VAR cheapest = Bids[Cheapest Bid]
RETURN
CALCULATE (
MIN ( Bids[Company Name] ),
ALLEXCEPT ( Bids, Bids[Foreign Key] ),
Bids[Bid] = cheapest
)Pat
Thank you mahoneypat!
- mahoneypat
Since you already have a column with cheapest bid, this DAX column expression will get your desired result. Replace Bids with your actual table name.
Lowest Bidder =
CALCULATE (
MIN ( Bids[Company Name] ),
ALLEXCEPT ( Bids, Bids[Foreign Key], Bids[Cheapest Bid] )
)Pat
Hi mahoneypat
Thanks for the contribution. Unfortunately what this expression does is calculate the company name by taking 'MIN' as 'first in alphabetical order', and disregards the value of the cheapest bid.
Therefore using the above example, Foreign Key 1234 would have displaed a Cheapest bidder of Fruit&Veg Ltd. and 5678 would have displaed a Cheapest bidder of Chewing Gum Plc., since they are both first alphabetically in the list of companies of their respective foreign keys.
