max and filter in same table

[eibcga2014]

I have three tables with the following columns:

BIDDER (fact table for list of bidders - there are only two bidders)
id_bidder (primary key)
bidder_name

STONE (fact table for list of stones - up to 100 stones)
id_stone (primary key)
stone_size
stone_weight

BID (bridge table for list of all bids placed, one from each bidder on each stone)
id_bid (primary key)
id_bidder (foreign key)
id_stone (foreign key)
bid_amount
bid_total=[bid_amount]*RELATED('STONE'[stone_weight])

A collection of diamonds (stones) are each bid on by two bidders. Each stone goes to the highest bidder. This is a many-to-many relationship.

Since each stone has two bids, one from each of the two bidders, and the stone goes to the highest bidder, how do I code this in Power Pivot? I tried using MAXX and FILTER functions, but no luck. I tried to convert each bid into a subset table and then select the max bid of the two bidders of each stone.

I would like to be able to just list in a pivot table the list of stones, with two columns, one for each bidder, which identifies who won the bid for each stone, and what the total amount the winning bidder has to pay.

Any guidance please?

 

[scottsen]

I am having a hard time seeing how this is a many to many. Are your Bidder and Stone tables actually both just lookup tables, to your (one) fact able "Bid" ?

How many rows in the Bid table? I ask because if its small (say, less than 100k rows), I would probably add some calculated columns to make it easier. For example: "IsWinningBid" sound easy and useful.

 

[eibcga2014]

Scottsen, that's one way to look at it. A stone can receive many bids, and a bidder can place bids on many stones. These bids are tracked in the Bids table.

For example, if there's 50 rows (unique stone records) in the Stone table, and two rows (unique bidder records) in the Bidder table, then the Bid table would have 100 rows (50 x 2). Bidder A places 50 bids, one for each stone, and Bidder B does the same. Each stone goes to the highest bidder.

My difficulty is writing the calculated field which considers the maximum bid for each stone when it's all in the same table. Thanks for your quick reply.

 

[scottsen]

If you drop id_stone on your pivot table rows, and =MAX(bid[bid_total]) (or bid_amount if you prefer) that will give you a per stone max bid.

Of course, if you drop bidders on columns (which you stated is what you want)... that would give the max each bidder did on that stone, which isn't what you want... I assume you want a blank if it wasn't the max bid?

I think something like this could work?

=IF(MAX(bid[bid_total]) = CALCULATE(MAX(bid[bid_total]), ALL(bidder[bidder_name])), MAX(bid[bid_total]), BLANK())

Basically, if the bid is the max bid across all bidder's... output that that bid, else output blank.

The grand total cell will give you something freaky, I can help w/ that when we get there

 

[eibcga2014]

The Max amount that is being returned in the calculated field in both your suggestions is the maximum bid amount of all the 100 bids placed, rather than taking the maximum for each of the 50 stone? This is the same problem I was having originally. :D

 

[scottsen]

I am assuming you are putting Stone on rows of your pivot table, and the Bidder Name on columns. In that case, the MAX will apply to just that initial filter context... so, a specific stone & bidder.

 

[Tianbas]

if its small (say, less than 100k rows), I would probably add some calculated columns to make it easier. For example: "IsWinningBid" sound easy and useful.

Back to the first post from Scott this could be the formula for a calculated columnd to identify the winning bids.

=if(bid[bid_amount]=calculate(max(bid[bid_amount]),All(bid),bid[id_stone]=earlier(bid[id_stone])),"Win","")


With that calculated column in a pivot filter you can easily sum the bid_total per bidder for the bids they have won

 

[eibcga2014]

Thank you for your reply. Unfortunately I get an #ERROR when I entered that formula (in the bid table).

 

[eibcga2014]

Yes. Stone ID, size, and weight as rows, and bidders as columns, and the calculated row as values per your IF formula above. The result is one row listing the stone that has the maximum bid of all bids in the bid table, rather than the maximum bid for each stone. Please forgive this beginner to Power Pivot. Is the issue because it's a many-to-many relationship, in such case the CALCULATE formula is needed to resolve the many-to-many issue?

 

[scottsen]

I have taken the ideas from Tianbas and myself and put them in this workbook. It looks good according to my understanding of your asks:
https://www.dropbox.com/s/uhuon9rm0eiw4z7/Bidding.xlsx?dl=0

The only exception is i excluded the grand total cells... cuz they are wrong. Easy to fix though, if the rest is good.