Forum Discussion

gusdcruz's avatar
gusdcruz
Icon for Helper I rankHelper I
7 years ago
Solved

Customer Segmentation based on products purchased

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

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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's avatar
    v-frfei-msft
    7 years ago

    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

3 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      v-frfei-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      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's avatar
        gusdcruz
        Icon for Helper I rankHelper I
        7 years ago

        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.