Forum Discussion

NazaCingolani's avatar
NazaCingolani
New Member
3 years ago
Solved

Calculating New Table for Product Combos

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!

 

Need Help
Tips and Tricks
  • DOLEARY85's avatar
    DOLEARY85
    3 years ago

    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 👍

     

4 Replies

  • DOLEARY85's avatar
    DOLEARY85
    Resident Rockstar
    3 years ago

    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's avatar
      NazaCingolani
      New Member
      3 years ago

      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's avatar
        DOLEARY85
        Resident Rockstar
        3 years ago

        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 👍