Calculate Customers with more than one order of X product

[akfir]

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

[Whitewater100]

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

[akfir]

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

[Whitewater100]

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

[akfir]

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)

[Whitewater100]

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?

[akfir]

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?

[Whitewater100 · Accepted solution]

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

[akfir]

Thanks! perfect!

[Whitewater100]

Excellent. Glad it worked out!

[akfir]

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%

[Whitewater100]

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. 

[akfir]

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

[Whitewater100]

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