Compare numbers of more than 2 rows

[daan1887]

Hi everybody,

I would be happy if some anyone of you guys would have a solution for my issue.

I need to compare prices of up to 5 sellers for ~ 5000 products. The final output should tell the user who has the cheapest price on a product level. 

Any ideas? Data structure and two possible output variants can be found below (I would prefer the first output, as it is more detailed).

Thanks in advance

Daniel

My data structure looks like this:

Seller | Product Price
Seller 1 | Product 1 | 5,99
Seller 2 | Product 1 | 8,99
Seller 3 | Product 1 | 6,99
Seller 4 | Product 1 | 5,99
Seller 5 | Product 1 | 5,99
Seller 1 | Product 2 | 18,99
Seller 2 | Product 2 | 20,99
Seller 3 | Product 2 | 20,99
Seller 4 | Product 2 | 20,99
Seller 5 | Product 2 | 20,99
Seller 1 | Product 3 | 2,99
Seller 2 | Product 3 | 2,99
Seller 3 | Product 3 | 4,99
Seller 4 | Product 3 | 
Seller 5 | Product 3 | 4,99

Needed output:

Product | Cheapest Offer
Product 1 | Seller 1, Seller 4, Seller 5
Product 2 | Seller 1
Product 3 | Seller 1, Seller 2

Alternative output:

Product | Cheapest Offer
Product 1 | Multiple sellers
Product 2 | Seller 1
Product 3 | Multiple sellers

[Zubair_Muhammad]

@daan1887

File attached as well

[edhans]

Impressive DAX formula @Zubair_Muhammad 

I've not messed with CONCATENATEX so I'll need to dig deeper into that one.

Side question: How did you upload a file? I'd rather attach files than links to my OneDrive account, but only see a photo/video button.

[Zubair_Muhammad]

Hi @edhans

This is one of the priveleges of being a Super User

Nice solution and Nice website

Keep up the good work

[edhans]

---

@Zubair_Muhammad wrote:

Hi @edhans

This is one of the priveleges of being a Super User

Nice solution and Nice website

Keep up the good work

---

Ahhh.... I'm just a muggle on this site, not a super user, so that explains it. 

[edhans · Accepted solution]

I used Excel to do this, but you can do it in Power BI just as easily. just depends on where your source data is.

This is best done in Power Query, not in DAX. This is the code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Seller", type text}, {"Product", type text}, {"Price", type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Price] <> null)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Product"}, {{"All Rows", each _, type table}}),
    #"Added Min Price record" = Table.AddColumn(#"Grouped Rows", "Min Price", each Table.Min([All Rows], "Price")),
    #"Expanded Min Price" = Table.ExpandRecordColumn(#"Added Min Price record", "Min Price", {"Price"}, {"Price"}),
    #"Added Get Best Price table" = Table.AddColumn(#"Expanded Min Price", "Best Prices", each let BestPrice = [Price]
in Table.SelectRows([All Rows], each [Price] = BestPrice)),
    #"Added Get Seller Name" = Table.AddColumn(#"Added Get Best Price table", "Get Seller Name", each Table.SelectColumns([Best Prices], "Seller")),
    #"Added Transpose table" = Table.AddColumn(#"Added Get Seller Name", "Transpose Table", each Table.Transpose([Get Seller Name])),
    #"Added Table to List" = Table.AddColumn(#"Added Transpose table", "Seller Names", each Table.ToList([Transpose Table], Combiner.CombineTextByDelimiter(", "))),
    #"Expanded Seller Names" = Table.ExpandListColumn(#"Added Table to List", "Seller Names"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Seller Names",{{"Seller Names", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Product", "Seller Names"})
in
    #"Removed Other Columns"

I basically grouped all of the data by product and put everything in an All Rows table. I then worked with each column one at a time as tiny nested tables until I got the end result, then expanded the final result for the seller names, and deleted all of my intermediate work.

Download my Excel file if you want to step through it and see how it works. I assumed a null price was to be excluded and filtered that out immediately.

By the way - this happens to be pretty close to one of the examples in the excellent book Magic Tricks for Data Wizards, which is free. Highly recommended.

[Zubair_Muhammad]

@daan1887

You can use this MEASURE as well

Cheapest Offer =
CONCATENATEX (
    TOPN ( 1, VALUES ( Table1[Seller ] ), CALCULATE ( MIN ( Table1[Price] ) ), ASC ),
    [Seller ],
    ", "
)