Classify customer based items purchased

[Anonymous]

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!

Customer	Product Bought	Customer Type (desired column)
A	Standard	Standard
A	Standard	Standard
A	Standard	Standard
B	Classic	Both
B	Standard	Both
B	Classic	Both
C	Classic	Classic
C	Classic	Classic
D	Premium	Premium

[amitchandak]

@Anonymous ,Please find the attached solution using measures

[Anonymous]

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. 

[amitchandak · Accepted solution]

@Anonymous ,

In such a case better to make it as the column, find the attached file.

Appreciate your Kudos.

[Ashish_Mathur]

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]

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]

Hi,

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

[amitchandak]

@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.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

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]

@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"
    )