Customer Classification

[Anonymous]

Hi all, I'm trying to implement the following login to classify customers in a new column but need some help.

PREMIUM : if all the Cloud products of a customer are 10 

PREMIUM ELIGIBLE : if all products (any Product Type) of a customer are in range : >= 5 and <10

NOT ELIGIBLE : if customer has any product < 4 (any Product Type)

PREMIUM PLUS : customers with products  > 11 (any Product Type)

Customer ID	Product Type	Product	Customer Type (desired column)
A	Cloud	10	Premium
A	Cloud	10	Premium
B	Cloud	5	Premium Eligible
B	Data Center	9	Premium Eligible
C	Data Center	3	Not Eligible
C	Data Center	9	Not Eligible
D	Cloud	11	Premium Plus
D	Cloud	11	Premium Plus

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

=if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Customer ID]=EARLIER(Data[Customer ID])&&Data[Product]<4))>0,"Not eligible",if(AND(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Customer ID]=EARLIER(Data[Customer ID])&&Data[Product Type]="Cloud"))*Data[Product]=CALCULATE(SUM(Data[Product]),FILTER(Data,Data[Customer ID]=EARLIER(Data[Customer ID])&&Data[Product Type]="Cloud"&&Data[Product]=10)),CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Customer ID]=EARLIER(Data[Customer ID])&&Data[Product Type]="Cloud"))>0),"Premium",if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Customer ID]=EARLIER(Data[Customer ID])&&Data[Product]>=5&&Data[Product]<10))>0,"Premium Eligible","Premium Plus")))

Hope this helps.

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

Per your request,the calculated column is as below:

Customer Type =
VAR c =
    CALCULATE (
        MIN ( 'Table'[Product] ),
        ALLEXCEPT ( 'Table', 'Table'[Customer ID] )
    )
VAR d =
    CALCULATE (
        MAX ( 'Table'[Product] ),
        ALLEXCEPT ( 'Table', 'Table'[Customer ID] )
    )
RETURN
    IF (
        'Table'[Product] = 10,
        "PREMIUM",
        IF (
            c >= 5
                && d < 10,
            "PREMIUM ELIGIBLE",
            IF (
                c <= 4
                    && d > 4,
                "NOT ELIGIBLE ",
                IF ( d >= 11, "PREMIUM PLUS ", BLANK () )
            )
        )
    )

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

=if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Customer ID]=EARLIER(Data[Customer ID])&&Data[Product]<4))>0,"Not eligible",if(AND(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Customer ID]=EARLIER(Data[Customer ID])&&Data[Product Type]="Cloud"))*Data[Product]=CALCULATE(SUM(Data[Product]),FILTER(Data,Data[Customer ID]=EARLIER(Data[Customer ID])&&Data[Product Type]="Cloud"&&Data[Product]=10)),CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Customer ID]=EARLIER(Data[Customer ID])&&Data[Product Type]="Cloud"))>0),"Premium",if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Customer ID]=EARLIER(Data[Customer ID])&&Data[Product]>=5&&Data[Product]<10))>0,"Premium Eligible","Premium Plus")))

Hope this helps.

[parry2k]

@Anonymous hey isn't we did similar in other post?