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?

 

[eibcga2014]

Thank you very much indeed, Scottsen and Tianbas.

I have downloaded the file.

How do you get the grand total cells to work?

 

[scottsen]

I have updated the file on dropbox.

=SUMX(VALUES(Stone[id_stone]), [Winning Bid])

 

[eibcga2014]

Your sumx formula seems to suggest that Winning Bid is a calculated field but it's supposed to be a measure? The pivot results only work when Winning Bid is a measure and not a calculated field. Not sure how to sumx on Winning Bid when it's a measure?

 

[scottsen]

In 2013 they called it a Calculated Field. In 2010, they called it a Measure. So... I'm not sure what you are asking.

 

[eibcga2014]

To me, the calculated field is when there's a result on every row in the table in the data view, showing the result of the calculation. A measure is when there is no calculated field but just one formula (a "summary field") in the "formula area" at the bottom part of the screen of data view, which can also be used in pivot tables. In other words, in the pivot table screen in Excel, the measures (summary fields) will appear with a small icon on the right of each field or dimension, where calculated fields do not have any icon next to them. When I try sumx with your formula, Pivot Table does not "see" your measure because it appears sumx only works with calculated fields. I'll download your file again and see your example. Just trying to learn why things work. Thanks.

 

[eibcga2014]

I've downloaded your revised file and I see how you added the Winning Bid measure to another measure. I learned something! Thanks!