cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ilhame Frequent Visitor
Frequent Visitor

After first sale what does customer buy

Hello Everyone, 

 

I am stuck at a level of my analysis and need some support Smiley Happy 

I am currently trying to answer the following question : 

After a new customer purchase a  specific product A do the same customer purchase the product B ? 

My table looks as follow : 

Date Transaction ID EmailItemquantity price
21/05/20181254jkj578community@gmail.com254618
22/05/20181254jkj579community@gmail.com254618
23/05/20181254jkj580community@gmail.com254618
24/05/20181254jkj581community@gmail.com254618
25/05/20181254jkj582community@gmail.com254618
26/05/20181254jkj583community@gmail.com254618

 

I used a measure from the member community  :

Measure =
VAR BoughtAfterAcquisition =
CALCULATE (
COUNTROWS ( 'Table1' ),
FILTER (
ALLEXCEPT ( 'Table1', 'Table1[Email], 'Table1d'[Date].[Year] ),
"2016" IN VALUES ( 'Table1'[item] )
&& "2010" IN VALUES ('Table1'[item])
)
)
VAR NOTSO =
CALCULATE (
COUNTROWS ( 'Table1' ),
FILTER (
ALLEXCEPT ( 'Table1',' Table1'[Email], 'Table1'[Date].[Year] ),
AND ( "2016" IN VALUES ( 'Table1'[External ID] ),"2010" IN VALUES ('Table1'[item]))
|| AND ( "2010" IN VALUES ('Table1'[External ID]), "2016" IN VALUES ( 'Table1'[item] ) ) ) ) RETURN
IF ( BoughtAfterAcquisition = 2, "BoughtAfterAcquisition", IF ( NOTSO = 1, "Not So super", "na" ) )
 
It give me the customer that bought both product, but the issue i have is that some customer may bought the product A without being 'New Customer', i don't know how to add the date of the first order to it so it only calculate customer that bought as first order the product A.
 
Thank you in advance ! 
 
Ilhame.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: After first sale what does customer buy

Hi @ilhame 

You may create below measures.Then use Final measure in the visual level filter to get the customers that buy product A as first product buy after the product B as second product.

Rank = RANKX(FILTER(ALL(Table1),Table1[Email]=MAX(Table1[Email])),CALCULATE(MAX(Table1[Date])),,ASC)
SecondProduct = CALCULATE(COUNTROWS(Table1),FILTER(ALLEXCEPT(Table1,Table1[Email]),[Rank]=2&&Table1[Product]="B"))
FirstProduct = 
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER (
      Table1,
        Table1[Product] = "A"
            && Table1[Date]
                = MINX (
                    FILTER ( ALL ( Table1 ), Table1[Email] = MAX ( Table1[Email] ) ),
                    Table1[Date]
                )
    )
)
Final = IF([FirstProduct]=1&&[SecondProduct]=1,1)

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Community Support Team
Community Support Team

Re: After first sale what does customer buy

Hi @ilhame 

You may try to create the measure like below:

Measure = 
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER (
        Table1,
        Table1[Product] = "A"
            && Table1[Date]
                = MINX (
                    FILTER ( ALL ( Table1 ), Table1[Email] = MAX ( Table1[Email] ) ),
                    Table1[Date]
                )
    )
)

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ilhame Frequent Visitor
Frequent Visitor

Re: After first sale what does customer buy

Hello Cherie Chen,

 

Thank you for your reply !!

 

Unfortunatly when i use both measure ( the one i showed and yours) it's not working at all, i get an error message...

 

I want to show on a table that customers that buy product A as first product buy after the product B or Not...

 

Thank you. 

 

Ilhame

 

 

 

Community Support Team
Community Support Team

Re: After first sale what does customer buy

Hi @ilhame 

You may create below measures.Then use Final measure in the visual level filter to get the customers that buy product A as first product buy after the product B as second product.

Rank = RANKX(FILTER(ALL(Table1),Table1[Email]=MAX(Table1[Email])),CALCULATE(MAX(Table1[Date])),,ASC)
SecondProduct = CALCULATE(COUNTROWS(Table1),FILTER(ALLEXCEPT(Table1,Table1[Email]),[Rank]=2&&Table1[Product]="B"))
FirstProduct = 
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER (
      Table1,
        Table1[Product] = "A"
            && Table1[Date]
                = MINX (
                    FILTER ( ALL ( Table1 ), Table1[Email] = MAX ( Table1[Email] ) ),
                    Table1[Date]
                )
    )
)
Final = IF([FirstProduct]=1&&[SecondProduct]=1,1)

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: After first sale what does customer buy

Hi,

Share some data and show the expected result.  Your original table does not have a product column.

ilhame Frequent Visitor
Frequent Visitor

Re: After first sale what does customer buy

Hi, 

Sorry for my late reply ...

It's worked !!!

 

thank you so much for the help !!

 

ilhame