cancel
Showing results for
Did you mean:
Helper IV

## Calculate Customers with more than one order of X product

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.

Amit

1 ACCEPTED SOLUTION
Solution Sage

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

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

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

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

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

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.

Weeks,

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

RETURN WorkingDays

13 REPLIES 13
Solution Sage

Hello:

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

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..
Helper IV

however, what is your [transactions] measure?

Solution Sage

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

Helper IV

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)

Solution Sage

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?

Helper IV

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?

Solution Sage

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

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

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

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

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

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.

Weeks,

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

RETURN WorkingDays

Helper IV

Thanks! perfect!

Solution Sage

Helper IV

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%
Solution Sage

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.

Helper IV

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
Solution Sage

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