Customer Segmentation based on products purchased

[gusdcruz]

I have a traditional sales sheet, name of the product, name of the customer, volume purchased and date of purchased. This file gets updated every single day. In the company we are looking into a program approach, where if a customer buys certain 3 products, not necessarly at the same time, let's same within the year; and those products contains the words "AMP", and the other product contains "LOL", and another product contains "HFR" this customer is a let's call a "SUPER" customer. If a customer buys only 2 of those products than this customer is "NOT SO SUPER".

any suggestion on how to accomplish this?

thank you

Gustavo

[v-frfei-msft · Accepted solution]

Hi @gusdcruz ,

Here I create a measure in my sample pbix.

Measure = 
VAR SUPER =
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[customer], Table1[date].[Year] ),
            "LOL" IN VALUES ( Table1[Product] )
                && "AMP" IN VALUES ( Table1[Product] )
                && "HFR" IN VALUES ( Table1[Product] )
        )
    )
VAR NOTSO =
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[customer], Table1[date].[Year] ),
            AND ( "LOL" IN VALUES ( Table1[Product] ), "AMP" IN VALUES ( Table1[Product] ) )
                || AND ( "LOL" IN VALUES ( Table1[Product] ), "HFR" IN VALUES ( Table1[Product] ) )
                || AND ( "HFR" IN VALUES ( Table1[Product] ), "AMP" IN VALUES ( Table1[Product] ) )
        )
    )
RETURN
    IF ( SUPER = 3, "Super", IF ( NOTSO = 2, "Not So super", "na" ) )

Pbix as attached.

Regards,

Frank

[v-frfei-msft · Accepted solution]

Hi @gusdcruz ,

Here I create a measure in my sample pbix.

Measure = 
VAR SUPER =
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[customer], Table1[date].[Year] ),
            "LOL" IN VALUES ( Table1[Product] )
                && "AMP" IN VALUES ( Table1[Product] )
                && "HFR" IN VALUES ( Table1[Product] )
        )
    )
VAR NOTSO =
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[customer], Table1[date].[Year] ),
            AND ( "LOL" IN VALUES ( Table1[Product] ), "AMP" IN VALUES ( Table1[Product] ) )
                || AND ( "LOL" IN VALUES ( Table1[Product] ), "HFR" IN VALUES ( Table1[Product] ) )
                || AND ( "HFR" IN VALUES ( Table1[Product] ), "AMP" IN VALUES ( Table1[Product] ) )
        )
    )
RETURN
    IF ( SUPER = 3, "Super", IF ( NOTSO = 2, "Not So super", "na" ) )

Pbix as attached.

Regards,

Frank

[v-frfei-msft · Accepted solution]

Hi @gusdcruz ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank

[gusdcruz]

Sorry, Frank. I havent had a chance to work on this. Other porjects come around since my initial request here. I will let you know as soon as I can. I really appreciate your time helping me out here.