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

[Anonymous]

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 no	Order no	Date	Time	Price$	Item no
123	A123	01.01.2016	08:00	3	C
123	A234	01.01.2016	12:00	1	A
234	A345	02.01.2016	09:00	3	C
234	A567	03.01.2016	08:00	2	B
345	A456	01.01.2016	10:00	3	C
345	A789	02.01.2016	09:00	1	A
123	A890	02.01.2016	12:00	1	A
456	A321	01.01.2016	12:00	3	C
456	A432	02.01.2016	12:00	1	A

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:

3		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!

[v-lili6-msft · Accepted solution]

hi, @Anonymous

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)

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]))

Best Regards,

Lin

[juli__sia123412]

It's not work correctly