Group Customers by Products Sold

[Anonymous]

I have a list of 1,000+ customers.  I need to quickly create 4 analytic groupings:

- Group 1: Customers that bought Product A and Not Product B

- Group 2: Customers that bought Product B and Not Product A

- Group 3: Customers that bought both Product A and B

- Group 4: Customers that bought neither Product A or B

Should this be done as a measure or a calculated column, and depending on which answer, what is the way to code the DAX or configure the query, respectively?

[LivioLanzo]

Hi @Anonymous

it can be done with a measure, can you pose a sample of your data?

[Anonymous]

Hi @LivioLanzo

Here's some excerpt data.  I added the "Custom Group" showing how I want the data grouped.  In Power BI, I can group on Customer Number, however the Customer list is 1,000+ and I'd like to create other customer groupings base on other Product purchase.  Ultimately, the data will be used on a Scatter plot to show Demand curves.

Customer Number	Product	Sales	Units	Custom Group
3100005	B	117,780	4	Group B
3100007	B	114,817	4	Group A&B
3100007	C	132,660	4	Group A&B
3100007	A	137,908	4	Group A&B
3100008	B	24,895	0	Group B
3100011	A	2,714	0	Group A
3100015	B	22,354	2	Group A&B
3100015	C	10,341	2	Group A&B
3100015	A	8,940	1	Group A&B
3100017	C	8,061	4	Group No A or B

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Please refer to below calculated column.

Custom Group =
VAR boughtA =
    IF (
        CALCULATE (
            COUNT ( Sheet1[Product] ),
            FILTER ( ALLEXCEPT ( Sheet1, Sheet1[Customer Number] ), Sheet1[Product] = "A" )
        ) > 0,
        TRUE (),
        FALSE ()
    )
VAR boughtB =
    IF (
        CALCULATE (
            COUNT ( Sheet1[Product] ),
            FILTER ( ALLEXCEPT ( Sheet1, Sheet1[Customer Number] ), Sheet1[Product] = "B" )
        ) > 0,
        TRUE (),
        FALSE ()
    )
RETURN
    IF (
        boughtA = TRUE (),
        IF ( boughtB = TRUE (), "GroupA&B", "GroupA" ),
        IF ( boughtB = TRUE (), "GroupB", "	Group No A or B" )
    )

Best regards,

Yuliana Gu