Max Bid

[cmac1]

I am new to DAX and am trying to do something that I think should be fairly simple.  I have an auction table where each row is a unique bid for a specific item.  Each bid has its own unique id, bid amount, bidder id, and references the item that is being bid on.  I need to find the bidder with the maximum bid for each item.  Any suggestions on how I can do that?

[v-shex-msft · Accepted solution]

Hi @cmac1,

You can simple use summarize function to get the max bid amount of each item:

Table 2 = SUMMARIZE(Sheet1,[bidder id],"Max Bid",MAX(Sheet1[bid amount]))

Regards,

Xiaoxin Sheng

[v-shex-msft · Accepted solution]

Hi @cmac1,

You can simple use summarize function to get the max bid amount of each item:

Table 2 = SUMMARIZE(Sheet1,[bidder id],"Max Bid",MAX(Sheet1[bid amount]))

Regards,

Xiaoxin Sheng

[cmac1]

Thank you.  I realized that I needed to use Power Query instead of DAX.  So my formula ended up like this:

= Table.Group(#"StepNumber1", {"Auction Items"}, {{"Max Bid", each List.Max([Bid Price]), type number}, {"# of Bids", each Table.RowCount(_), type number}})

The results are accurate but I am missing an important piece.  I need to know who the Max Bidder is.  I have the Max Bid amount but I don't know who bidder for that amount was.  The bidder ID is in my table I just need to make it visible in this table.  Any ideas?

[TomMartens · Accepted solution]

Hey,

in this example I used Power Query to find the max amount for an item, I started with this table (fact)

If you open the query editor and look at the query for the the table fact, you will see a step called "Group Rows (to be deleted)", here I used "Transform - Group By". You can delete this step if you want, it is used just for demonstration in this example :-):

 

If you have a look at the Formula Bar you will see this:

= Table.Group(#"Changed Type", {"itemid"}, {{"groupIndex", each _, type table}})

Here you will find " each _" the underscore represents each row of the base table, if you look at the step "Grouped Rows" you will see that I have replaced the underscore with a nested function call "Table.AddIndexColumn( Table.Sort(...))". Be aware that you have to replace the underscore in the Formula Bar. If you did the replacement, the grouping dialog will not reappear.

This tweaking is necessary to group by itemid but not to loose the column someoneid (in SQL Server you achieve this by using windowing functions (aggregating without loosing the detail rows).

This nested function call creates an IndexColumn. The Index restarts in each group. The function Table.Sort() sorts the rows in each group in descending order by the column amount

{{"amount", 1}}

 the 1 represents descending order whereas 0 means ascending order.

Than I

• expanded the table,
• renamed the column
• filtered the rows where the column value is 1
• merged the table with the table "someone" using the column someoneid to retrieve the value of the column someone

Hope this helps 

[cmac1]

That worked great!  Thank you!

[cmac1]

I was able to get the formula setup using DAX also.  

BidSummary = SUMMARIZE(AuctionBids,AuctionBids[Auction Items],"Max Bid", MAX(AuctionBids[Bid Price]),"# of Bids",COUNT(AuctionBids[Bidder]))

But I have the same problem:  I also need to know who the winning bidder was?  I have the bidder ID in another table, I just need to have it available in this table.  Any ideas?

[Anonymous]

Hi @cmac1,

You can create a calculated column like below:

is highest bid = IF(
	RANKX(
		FILTER(
			data, EARLIER(data[item id])=data[item id]
		),
		data[bid amount],,
		DESC
	) = 1,
	1,
	0
)

This column will contain 1 for the highest bid for each individual item id.

HTH,

Pawel