Segment customers by amount of orders

[Anonymous]

Happy New Year everyone!

I have a table with order date, customer ID, order number and the ID’s of the items that were part of the order.

Order date	Customer ID	Order Number	Item ID
1-1-2019	AA	X01	Z1
1-1-2019	AA	X01	Z2
1-1-2019	BB	X02	Z3
1-1-2019	BB	X02	Z4
1-1-2019	BB	X02	Z5
1-1-2019	CC	X03	Z5
1-1-2019	AA	X04	Z3

And I have another table just with the customer ID’s and some metadata for each customer. The two tables are linked with the Customer ID as the key

Customer ID	Country	City
AA	-	-
BB	-	-
CC	-	-
DD	-	-
EE	-	-
FF	-	-
GG	-	-

What I want to do eventually is create a measure (or several measures) that’s going to tell me for any given month how many customers there were that placed X orders, like this:

Month	Customers that placed more than 1 order	Customers that placed 1 order	Customers that placed 0 orders
January 2019	1 (AA)	2 (BB and CC)	4 (DD EE FF and GG)
February 2019	-	-	-

How do I do that?

[v-lili6-msft · Accepted solution]

hi, @Anonymous

After my research, you could try this way as below:

Step1:

Add year and month column for order date.

Year = YEAR(Table1[Order date])

Month = MONTH(Table1[Order date]) 

Step2:

Use these three formulae as below:

Customers that placed more than 1 order = var _table=SUMMARIZE(Table1,Table1[Year],Table1[Month],Table1[Customer ID],"_times",DISTINCTCOUNT(Table1[Order Number])+0) return
CALCULATE(COUNTAX(FILTER(_table,[_times]>1),[Customer ID]))

Customers that placed 1 order = var _table=SUMMARIZE(Table1,Table1[Year],Table1[Month],Table1[Customer ID],"_times",DISTINCTCOUNT(Table1[Order Number])+0) return
CALCULATE(COUNTAX(FILTER(_table,[_times]=1),[Customer ID]))

Customers that placed 0 orders = var _table=SUMMARIZE(Table1,Table1[Year],Table1[Month],Table1[Customer ID],"_times",DISTINCTCOUNT(Table1[Order Number])+0) return
CALCULATE(COUNTA(Table2[Customer ID])-CALCULATE(COUNTAX(_table,[Customer ID])))

Result:

and here is pbix file, please try it.

Best Regards,

Lin

[Anonymous]

Hi @v-lili6-msft

Thanks so much for providing this solution in such detail! It works perfect and it helps me in learning some new DAX tricks.

Best regards

Bas