Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Classify customer based items purchased

I'd like to be able to classify, and then filter customers in a report based on what they purchased. My customer criteria is:
1. if a customer has only purchased Standard products, then they are a "Standard" customer

2. if a customer has only purchased Classic products, then they are a "Classic" customer

3. if they purchased both Classic and Standard products, then they are a "Both" customer

4. if they purchased a Premium product, then they are a "Premium' customer

 

In this case, customer A = Standard, customer B = both, customer C = Classic, customer D = Premium. I want to create a calculated column stating the type of customer they are (Standard, Classic, Both, or Premium)

 

Any suggestions on how to accomplish this? Thank you!

CustomerProduct BoughtCustomer Type (desired column)
AStandardStandard
AStandardStandard
AStandardStandard
BClassicBoth
BStandardBoth
BClassicBoth
CClassicClassic
C ClassicClassic
DPremiumPremium

 

 

Need Help

10 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks amitchandak! One last thing - how would I create a filter using the Customer Type Recal measure? I want to know how many customers there are, and the breakdown by customer type. 

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Greg_Deckler , thanks for your response! Could you take a look at my modified post? I actually realized I want to create a calculated column called Customer Type (standard, classic, both, or premium). Please let me know if you know of a way to do this in Power BI. 

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        Anonymous You can create a column like this:

         

        Customer Type = 
    VAR __Table = 
        SUMMARIZE(
            FILTER(
                'Table',
                [Customer] = EARLIER([Customer])
            ),
            [Product Bought]
        )
RETURN
    SWITCH(TRUE(),
        "Premium" IN __Table,"Premium",
        "Standard" IN __Table && "Classic" IN __Table,"Both",
        "Classic" IN __Table,"Classic",
        "Standard" IN __Table,"Standard"
    )

         

         

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous ,

    try all 4 as measures

    all= calculate(distinctCOUNT(table[Product Bought]))
Standard =calculate(distinctCOUNT(table[Product Bought]),	Product Bought="Standard")
Classic =calculate(distinctCOUNT(table[Product Bought]),	Product Bought="Classic")

flag =
Switch(true(),
[all]=[Standard],"Standard",
[all]=[Classic],"Classic",
"Both"
)

     


    Appreciate your Kudos.

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    Write this calculated column formula

    Category = if(CALCULATE(DISTINCTCOUNT(Data[Product Bought]),FILTER(Data,Data[Customer]=EARLIER(Data[Customer])))=1,Data[Product Bought],"Both")

    Hope this helps.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Ashish_Mathur, thank you for the response. With your solution I was unable to type in a table column after the Earlier function for some reason. Could you also take a look at my edited post and see if your potential solution might be any different than your original post?

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        6 years ago

        Hi,

        As you can see, my formula works very well.  Mine is a calculated column formula.