Calculating New Table for Product Combos

[NazaCingolani]

So, here is a sample dataset of how my Sales Table is:

Sales Table:

What I need to do, is understand which are the most common "product combinations" based on this table. An example of the "desirable" output table would be something like the below screenshot:

Product Combos Table:

Any help will be much appreciated!

Thanks!

[DOLEARY85]

Hi,

try this calculated column:

Column =

CONCATENATEX (

    CALCULATETABLE (

        VALUES ( 'Table (2)'[Product Type] ),

        ALLEXCEPT ( 'Table (2)', 'Table (2)'[Order ID] )

    ),

    [Product Type],

    " & ",

    [Product Type], ASC

)

original (right) calculated column (left)

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[NazaCingolani]

Hi! First of all, thanks for your help.

So, I added the calculated column to my "Sales Table", so now I get the concatenate of the products that were part of that order.

Then, I create a table in Power BI adding the new calculated column (AKA Product Combo), and add the OrderID value as a distinct count, and I get the outcome of how many orders had that combo.

The only thing, is that when an Order has more than "1" product as part of the combo, that is not been considered.

Example:

Order Z: 2 x product A + 3 x Product B
Outcome should be A & A & A & B & B, now im only getting A & B.

Is there a way we could implement this consideration based on the Net Quantity from Sales Table?

[DOLEARY85 · Accepted solution]

Ah okay,

try this one:

I've highlighted the only change to the calculated column

Column =

CONCATENATEX (

    CALCULATETABLE (

        ADDCOLUMNS('Table (2)',"Product",'Table (2)'[Product Type]),

        ALLEXCEPT ( 'Table (2)', 'Table (2)'[Order ID] )

    ),

    [Product Type],

    " & ",

    [Product Type],ASC

)

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[NazaCingolani]

Amazing. It worked like a charm!

One final thing. If I would like to exclude a product from this formula, where should the filter be applied/hardcode?