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 ...
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 👍

     
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 👍

       
      • NazaCingolani's avatar
        NazaCingolani
        New Member
        3 years ago

        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?