Customers who bought multiple products

[WillT]

Hi everyone. Worked on a little problem recently to try and figure out "customers who bought combinations of products". I have seen plenty of posts and great patterns on Basket Analysis (e.g. DAX Patterns, Chris Webb etc.) but they were always about a max of two products and used a pattern with multiple 'dimension' tables and meant you had to deal with inactive relationships.

We found a nice way of simplifying this so with one slicer you can select a few products and see how many people bought all of those products, and how many bought any of them:

https://app.powerbi.com/view?r=eyJrIjoiMmQ0OWYzYmQtNjhjNC00OGRiLWEzNGQtNGZjYWU4YWQzMTg1IiwidCI6ImFmZ... 

 You can get the pbix file here:

https://1drv.ms/u/s!AhrHQG3fCq7Rsu0Z8MhPKuWN3nkt6A

Hope you find it useful!

[KrishnaKalyan]

Hi,

I have a similar request, but my users want to see the customer list and the amounts per product as well in a pivot table

My data has Columns: Year, Customer No, Customer Name, Product, and Amount.

I'm thinking if I can get a unique customer list in a calculated table dynamically based on the product selection and year selection, I can use the relations between the table and get the visualizations working.

Could someone kindly help in getting this dynamic table? Even if anyone has a better solution, I would be more than happy to take it. 

Thank You & Regards,

Krishna Kalyan

[Vvelarde]

Hi, In the PowerBI Sample you can't select more than 1 product.

EDIT: Sorry, Its correct.

[Sean]

@Vvelarde Not sure what you mean - you can CTRL select or just turn off single select (in the pbix file)

EDIT: CTRL Select works on the published report

[Sean]

Here's the Measure for everyone to see!

Buyers of All Selected Products =
IF (
    ISBLANK (
        COUNTROWS (
            FILTER (
                SUMMARIZE (
                    Sales,
                    Sales[Customer],
                    "ProductsBought", DISTINCTCOUNT ( Sales[Product] )
                ),
                [ProductsBought] = COUNTROWS ( VALUES ( Sales[Product] ) )
            )
        )
    ),
    0,
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                Sales,
                Sales[Customer],
                "ProductsBought", DISTINCTCOUNT ( Sales[Product] )
            ),
            [ProductsBought] = COUNTROWS ( VALUES ( Sales[Product] ) )
        )
    )
)

Thanks to @WillT 

[Anonymous]

Hi,

This Solution is working for me and going fantastic but can i show the name of customers who bought all products also?

I am unable to show the customers name with this DAX.

Please help

Thank you!

[WillT]

Wow, you jumped on this quickly! I've just updated the report so the slicer multi-selects by default 🙂 And thank you Sean for pulling the formula out!

I'd love to see if any of the DAX gurus around here can find a more efficient way to do this...

[OwenAuger]

HI there,

Here are a few options I came up with 🙂

The last one (v4) seems to run fastest but please test at your end with actual data.

1. On your existing measure, you can get rid of the ISBLANK test and just add zero (since Blank + Zero = Zero):
   

   Buyers of All Selected Products v2 =
   COUNTROWS (
       FILTER (
           SUMMARIZE (
               Sales,
               Sales[Customer],
               "ProductsBought", DISTINCTCOUNT ( Sales[Product] )
           ),
           [ProductsBought] = COUNTROWS ( VALUES ( Sales[Product] ) )
       )
   )
       + 0

2. Here is an alternative using EXCEPT on two lists of products to see if removing the selected products from each customer's list leaves an empty list (rather than comparing product counts):
   

   Buyers of All Selected Products v3 =
   COUNTROWS (
       FILTER (
           VALUES ( Sales[Customer] ),
           ISEMPTY (
               EXCEPT (
                   VALUES ( Sales[Product] ),
                   CALCULATETABLE ( VALUES ( Sales[Product] ) )
               )
           )
       )
   )
       + 0

3. This version is a bit convoluted but seems to run fastest. The SUMMARIZE(GENERATE(...)) part returns a list of customers who didn't buy all selected products, then the outer EXCEPT takes the difference between the full customer list and this list, leaving customers who did buy all selected products. 
   

   Buyers of All Selected Products v4 =
   COUNTROWS (
       EXCEPT (
           VALUES ( Sales[Customer] ),
           SUMMARIZE (
               GENERATE (
                   VALUES ( Sales[Customer] ),
                   EXCEPT (
                       VALUES ( Sales[Product] ),
                       CALCULATETABLE ( VALUES ( Sales[Product] ) )
                   )
               ),
               Sales[Customer]
           )
       )
   )
       + 0

All the best,

Owen

[SverreK]

Hi, I have a table also containing the price of all the different sales and want to calculate the total sale on the customers who buy all the selected products. Is there a way to change the DAX to make a sum of sales instead of a count of customers buying the selected products?

[alexanderkall]

Wondering if anyone has found a solution to this?

[OwenAuger]

In case it's of interest, I used this dummy DAX Query to test performance of the different measures in DAX Studio.

It evaluates the chosen measure for every permutation of 5 products (with repetitions).

Measures v3 & v4 are noticeably faster.

EVALUATE
ADDCOLUMNS (
    CROSSJOIN (
        SELECTCOLUMNS ( VALUES ( Sales[Product] ), "Product 1", Sales[Product] ),
        SELECTCOLUMNS ( VALUES ( Sales[Product] ), "Product 2", Sales[Product] ),
        SELECTCOLUMNS ( VALUES ( Sales[Product] ), "Product 3", Sales[Product] ),
        SELECTCOLUMNS ( VALUES ( Sales[Product] ), "Product 4", Sales[Product] ),
        SELECTCOLUMNS ( VALUES ( Sales[Product] ), "Product 5", Sales[Product] )
    ),
    "Measure value", CALCULATE (
        /* Replace with test measure */
        [Buyers of All Selected Products v4],
        Sales[Product] = EARLIER ( [Product 1] )
            || Sales[Product] = EARLIER ( [Product 2] )
            || Sales[Product] = EARLIER ( [Product 3] )
            || Sales[Product] = EARLIER ( [Product 4] )
            || Sales[Product] = EARLIER ( [Product 5] )
    )
)

Owen 🙂