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

Customers who bought this the first time bought this the second time

Hi,

 

I have been trying to solve this, but all my ideas fails. 

 

I have a dataset showing what customers bought at a store. It holds one item per line, and can be IDed by customer number, order number and item number.  It can be illustrated like this:

Customer noOrder noDateTimePrice$Item no
123A12301.01.201608:003C
123A23401.01.201612:001A
234A34502.01.201609:003C
234A56703.01.201608:002B
345A45601.01.201610:003C
345A78902.01.201609:001A
123A89002.01.201612:001A
456A32101.01.201612:003C
456A43202.01.201612:001A

 

I want to count number of customers who bought f.ex. C in their first order and A in their second order, and visualize it like this:

   
  1
   
CA CB

 

Where the first column, CA, goes to a hight of 3, and the second column goes to a hight of 1, etc. How can this be done?

 

Thank you for your imput and suggestions!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Customers who bought this the first time bought this the second time

hi, @nordgard

After my research, you could do these follow my steps as below:

Step1:

Combine date column and time column into datetime column

date time = 'Table'[Date]+'Table'[Time] 

Step2:

Add a rank column for each customer by datetime

customer rank = RANKX(FILTER('Table','Table'[Customer no]=EARLIER('Table'[Customer no])),'Table'[date time],,ASC,Dense)

2.JPG

Step3:

Add a new calculate table for customers

Table 2 = VALUES('Table'[Customer no])

Step4:

Add first time bought item no and  second time bought item no

first time = CALCULATE(MAX('Table'[Item no]),FILTER('Table','Table'[Customer no]='Table 2'[Customer no]&&'Table'[customer rank]=1))


second time = CALCULATE(MAX('Table'[Item no]),FILTER('Table','Table'[Customer no]='Table 2'[Customer no]&&'Table'[customer rank]=2))

Step5:

Add a first and second column

first&second = 'Table 2'[first time]&'Table 2'[second time]

Step6

add a count customers measure and drag it into visual

countcustomers = CALCULATE(COUNTA('Table 2'[Customer no]))

3.JPG

 

 

Best Regards,

Lin

 

 

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

Re: Customers who bought this the first time bought this the second time

hi, @nordgard

After my research, you could do these follow my steps as below:

Step1:

Combine date column and time column into datetime column

date time = 'Table'[Date]+'Table'[Time] 

Step2:

Add a rank column for each customer by datetime

customer rank = RANKX(FILTER('Table','Table'[Customer no]=EARLIER('Table'[Customer no])),'Table'[date time],,ASC,Dense)

2.JPG

Step3:

Add a new calculate table for customers

Table 2 = VALUES('Table'[Customer no])

Step4:

Add first time bought item no and  second time bought item no

first time = CALCULATE(MAX('Table'[Item no]),FILTER('Table','Table'[Customer no]='Table 2'[Customer no]&&'Table'[customer rank]=1))


second time = CALCULATE(MAX('Table'[Item no]),FILTER('Table','Table'[Customer no]='Table 2'[Customer no]&&'Table'[customer rank]=2))

Step5:

Add a first and second column

first&second = 'Table 2'[first time]&'Table 2'[second time]

Step6

add a count customers measure and drag it into visual

countcustomers = CALCULATE(COUNTA('Table 2'[Customer no]))

3.JPG

 

 

Best Regards,

Lin

 

 

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

Re: Customers who bought this the first time bought this the second time

It's not work correctly 

Screenshot_4.png