Forum Discussion
Max Bid
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
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
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?
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
That worked great! Thank you!