topic Re: Calculate Customers with more than one order of X product in DAX Commands and Tips

Calculate Customers with more than one order of X product

akfir — Fri, 27 May 2022 20:21:14 GMT

Hello masters,
I wish to create a measure which calculates the number of customers who REPURCHASED a product = bought X product in more than 1 different orders (in a dynamic range of dates).
Please note that a repurchase occures when a product is purchased by a specific customer in more than 1 order (2/3/4.....).
it actually counts the customers who bought x product in more than 1 and above order IDs.
Below example of data and the desired measure result:

Date	Order ID	Customer ID	Product
01/01/2022	AAA	111	x
01/01/2022	AAA	111	y
03/01/2022	BBB	111	x
04/01/2022	CCC	222	y

In this range of dates - product x was repurchased by 1 customer (111) , product y was not repurchased by any customer.

thanks in advance champs,
Amit

Re: Calculate Customers with more than one order of X product

Whitewater100 — Fri, 27 May 2022 21:02:14 GMT

Hello:

I happen to have a file on this topic. Please see additional measure at bottom.

https://drive.google.com/file/d/1Gu_IBivGw5eOtOUs1bnloNkD6NjmQxDo/view?usp=sharing

For Calc Col :

Multi-purchase CC =

var vproduct = Data[Product]

return

CALCULATE(

COUNTROWS(Data),

Data[Product] =vproduct,

ALLEXCEPT(Data, Data[Customer ID]))

The main measure is:

Customers w/Multi Purchases =

COUNTROWS(

FILTER(

SUMMARIZE( Cust_Sales, Cust_Sales[Product],

"Total Purchases", [Transactions] ),

[Total Purchases] >= 2 ) )

However, you should see model design to fully understand. I hope this solves your question. Thanks..

Re: Calculate Customers with more than one order of X product

akfir — Fri, 27 May 2022 21:39:56 GMT

Thanks for your response.
unfortunatelly i am unable to download your shared PBI file due to business restrictions.
however, what is your [transactions] measure?

Re: Calculate Customers with more than one order of X product

Whitewater100 — Sat, 28 May 2022 12:36:15 GMT

Hi:

I see. This measure is just a COUNTROWS of the Fact Table. I'll paste the model image below.

Transactions = COUNTROWS(Cust_Sales)

You can also just add a measure if you are working in one table:

Multi Purchase Final =

var multi = SELECTEDVALUE(Data[Multi-purchase CC])

return

IF(multi>1, MAX(Data[Multi-purchase CC]))

Re: Calculate Customers with more than one order of X product

Whitewater100 — Sat, 28 May 2022 13:03:23 GMT

Hi:

You want no. customers. Now you have how many purchases per item and no of customers with more than one purchase, mage below. I added this measure:

Customers w/Multiply Purchases (Data) =

var ct = SELECTEDVALUE(Data[Multi-purchase CC])

return

IF(ct > 1,CALCULATE(DISTINCTCOUNT(Data[Customer ID])))

Re: Calculate Customers with more than one order of X product

akfir — Sat, 28 May 2022 14:43:05 GMT

it didnt work unfortunately.
all data i have is one table (customers and orders). i wish to count unique customers who purchased x product (there are 8 available) in more than one order (order id)

Re: Calculate Customers with more than one order of X product

Whitewater100 — Sat, 28 May 2022 16:36:05 GMT

Hi akfir:

The data you provide is below. My table has one customer (ID 111) who had repeat customers.

Date	Order ID	Customer ID	Product
01/01/2022	AAA	111	x
01/01/2022	AAA	111	y
03/01/2022	BBB	111	x
04/01/2022	CCC	222	y

My previous email provided this solution.

It appears to match your example:

In this range of dates - product x was repurchased by 1 customer (111)

I'm not following as I only see 4 records with two products?

Re: Calculate Customers with more than one order of X product

akfir — Sat, 28 May 2022 17:18:50 GMT

can you send your project privately by email? (amitkfir7733@gmail.com)
my aim is showing this table and measures:

product	Total Customers Purchased	Share of Total Customers	Num. Of Orders	Customers Repurchased	Repurchase Rate
x	1	50%	2	1	100%
y	2	100%	2	0	0%
Total	2	100%	3	1	50%

Re: Calculate Customers with more than one order of X product

Whitewater100 — Sat, 28 May 2022 18:12:48 GMT

Yes, I will now. Please look at page "UG". The first two pages might be interesting to you as well. Thank you.

Please mark as solution when you verify.

Re: Calculate Customers with more than one order of X product

akfir — Sat, 28 May 2022 18:24:39 GMT

Thanks for sending your project!
after trying to implement it in my project and getting constant zeros for "Multi Purch Customer CT" for each product, i figured out that in your project - and in mine as well - this measure does not change according to date slicer.

can you please check this out?

Re: Calculate Customers with more than one order of X product

akfir — Sat, 28 May 2022 18:51:45 GMT

please note that x orderid can contain several rows for same product, so COUNTROWS is not usefull in this case:

Order	Product	Customer	Quantity
AAA	x	111	1
AAA	x	111	1
AAA	x	111	1
AAA	x	111	1
AAA	x	111	1
			5

Re: Calculate Customers with more than one order of X product

Whitewater100 — Sat, 28 May 2022 20:21:03 GMT

Hi:

Yes, please connect date to current date table. Use new date table for slicer. I'llpaste Date Table code below. Mine changes when I select Jan 1-2-3 or 4.

Please mark as Date Table. I called this second date table(the first one was just outdated) "Dates 2"

Dates 2=

-- Specify a start date and end date

VAR StartDate = Date(2021,1,1)

VAR EndDate = Today() + 243

VAR FiscalMonthEnd = 12

-- Generate a base table of dates

VAR BaseTable = Calendar(StartDate, EndDate)

-- Add the Year for each individual date

VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))

-- Add the calendar month and other month related data for each date

VAR Months = ADDCOLUMNS(

Years,

"Month",MONTH([Date]),

"Year and Month Number",FORMAT([Date],"YYYY-MM"),

"Year and Month Name",FORMAT([Date],"YYYY-MMM"),

"Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),

"Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),

IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))

)

-- Add the Quarter and other quarter related data for each date

VAR Quarters = ADDCOLUMNS(

Months,

"Quarter",ROUNDUP(MONTH([Date])/3,0),

"Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))

-- Add the Day and other day related data for each date

VAR Days = ADDCOLUMNS(

Quarters,

"Day",DAY([Date]),

"Day Name",FORMAT([Date],"DDDD"),

"Day Of Week",WEEKDAY([Date]),

"Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)

-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date

VAR Weeks = ADDCOLUMNS(

Days,

"Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,

"Week of Year (Sunday)",WEEKNUM([Date],1),

"Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))

-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.

var WorkingDays = ADDCOLUMNS(

Weeks,

"Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})

RETURN WorkingDays

Re: Calculate Customers with more than one order of X product

akfir — Sat, 28 May 2022 20:24:17 GMT

Thanks! perfect!

Re: Calculate Customers with more than one order of X product

Whitewater100 — Sat, 28 May 2022 20:28:13 GMT

Excellent. Glad it worked out!