Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Customer Classification

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 IDProduct TypeProductCustomer Type (desired column)
ACloud10Premium
ACloud10Premium
BCloud5Premium Eligible
BData Center9Premium Eligible
CData Center3Not Eligible
CData Center9Not Eligible
DCloud11Premium Plus
DCloud11Premium Plus
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

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

    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!

     

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    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's avatar
    v-kelly-msft
    Community Support
    6 years ago

    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!