advanced filtering - Select a group of customers

[Anonymous]

Hello!

I need help segmenting customers by groups of 10 according the sales (Desc).

I rank all my customers in groups of 10 customers each ( according to sales), so when I select a group of 10 customers I only want to see the results for that 10 customers.

I created this table:

Group Sales      Min                       Max

Top 10	0		10
Top [11 - 20]	10		20
Top [21 - 30]	20		30
Top [31 - 40]	30		40
Top [41 - 50]	40		50
Not Top 50	100		10000

I am trying to do the cohort analasys for only that 10 customers, comparing the first buy and the next one, and relating it to period of purchase.

The result of my analysis is the Retention Pediod, the measure is:

Retention Period =

VAR CustomerDimension = Values( SALES [customer_name])

RETURN

IF ( SELECTEDVALUE ( 'Cohort Periods'[Period]) = 0; [UNIQUE CUSTOMERS];

//CALCULATE(

CALCULATE([UNIQUE CUSTOMERS];       

     FILTER (CustomerDimension;

         COUNTROWS (

             FILTER ( 'Cohort Periods' ;

[Retention Days] > 'Cohort Periods'[Min Days] && [Retention Days] <= 'Cohort Periods'[Max Days]))>0))//;

//TREATAS(VALUES(Dates[MonthnYear]); 'Cohort'[join Month Cohort])))

I already created the cohort analysis, but only works for all customers, and it can't be filter by the Group of 10 customers.

So I think the error on the 2nd line of the DAX measure above

VAR CustomerDimension = Values( SALES [customer_name]) => its not filtering only for 10 customers

Can u please help?

Thanks 

Telmo

[v-yulgu-msft]

Hi @Anonymous,

Please create an extra measure:

Measure1 =
VAR sumsales =
    CALCULATE ( SUM ( SALES[sales] ), ALLEXCEPT ( SALES, SALES[customer_name] ) )
RETURN
    IF (
        sumsales >= SELECTEDVALUE ( GroupCustomer[Min] )
            && sumsales < SELECTEDVALUE ( GroupCustomer[Max] ),
        1,
        0
    )

Then, modify the formula for [Retention Period] as below.

Retention Period =
VAR CustomerDimension =
    FILTER ( VALUES ( SALES[customer_name] ), [Measure1] = 1 )
RETURN
    IF (
        SELECTEDVALUE ( 'Cohort Periods'[Period] ) = 0,
        [UNIQUE CUSTOMERS],
        CALCULATE (
            [UNIQUE CUSTOMERS],
            FILTER (
                CustomerDimension,
                COUNTROWS (
                    FILTER (
                        'Cohort Periods',
                        [Retention Days] > 'Cohort Periods'[Min Days]
                            && [Retention Days] <= 'Cohort Periods'[Max Days]
                    )
                ) > 0
            )
        )
    )

Regards,

Yuliana Gu

[Anonymous]

Hello v-yulgu-msft !

Thank u for trying to help.

It doesn´t work however, because when I am selectig the group ( for example TOP 10 customers) it doesnt filter only those 10 customers.

Maybe the new measure1 = has to be related to the rank oof the customer and not the customer name?

Can u also explain why u used this  ALLEXCEPT ( SALES, SALES[customer_name] ) )

thank u !

If possible could I sent u by private msg the file with my data and my model wtih masked sensible data of course?

Telmo

[v-yulgu-msft]

Hi @Anonymous ,

For better understanding about ALLEXCEPT, please have a look at this blog.

Since you have created a sample file with masked sensible data, you could share it via OneDrive and paste the download link here. Remeber to show us your desired output.

Regards,

Yuliana Gu