Solved: Repeat Customer - Page 4

morab · ‎10-13-2019

Hi,

Good Day!

Following is the data table:

OrderDate	Distributor Code	OutletCode	SRName	Category	SKUCode	OrderKG	SalesKG
9/1/2019	14413	100033897	Shipon	DDP	403	0.48	0.48
9/2/2019	14413	100033897	Shipon	DPP	404	1.44	1.44
9/3/2019	14413	100033897	Shipon	DDP	405	0.96	0.96
9/3/2019	14413	100033897	Shipon	DDP	403	0.96	0.96
9/1/2019	14413	100033885	Shipon	DPP	404	1.44	1.44
9/2/2019	14413	100033885	Shipon	DDP	405	0.96	0.96
9/3/2019	14413	100033899	Shipon	DDP	403	0.48	0.48

Based on above table, want to calculate following result by using DAX measures in power BI

By Category, number of repeat customer:

Category	Repeat Customer #
DDP	1
DPP	0
Total	2

Thanks in advance 🙂

need help

lc_finance · ‎10-13-2019

Hi @morab ,

below is my proposed solution: a formula repeatCustomer that counts the repeat customer based on the chosen category.

RepeatCustomer = 
VAR customerTable = ADDCOLUMNS(VALUES('Table'[OutletCode]), 
"numberOfPurchases", CALCULATE(COUNTX('Table', [OutletCode])))

RETURN 
SUMX(customerTable, IF([numberOfPurchases]>1,1,0))

And here is a screenshot:

I hope this is what you were looking for.

Regards,

LC

Interested in Power BI finance templates? Check out my blog at www.finance-bi.com

View solution in original post

lc_finance · ‎10-14-2019

Hi @morab ,

I believe I found the difference: in Excel, you are counting as 'repeat customers' only customers that buy on at least 2 different dates. The formula I proposed counts instead as 'repeat customers' any customer that has more than one transaction, even if the transaction is on the same date.

As an example, outlet code 100032940 for category IFFO: this outlet bought 2 times both on September 26. This customer (2 transactions but on the same day) is not counted in the Excel, but is counted in Power BI.

To have a count based on different days only, I had to add a calculated table to the model. Here is the formula for the table:

Customer Table by Date = 
SUMMARIZE('Datatable','Datatable'[OutletCode],'Datatable'[OrderDate], 'Datatable'[Category])

And here is the new measure, counting repeat customers based on different days:

RepeatCustomerDifferentDays = 
VAR selectedCategory = SELECTEDVALUE('Datatable'[Category])

VAR customerTable = 
CALCULATETABLE (
ADDCOLUMNS(VALUES('Customer Table by Date'[OutletCode]), 
"numberOfPurchases", CALCULATE(COUNTX('Customer Table by Date', [OutletCode])))
, 'Customer Table by Date'[Category]= selectedCategory
)

RETURN 
SUMX(customerTable, IF([numberOfPurchases]>1,1,0))

The result now matches the Excel:

The result now matches the Excel.

Here is the link for the PBI file: https://drive.google.com/open?id=1tjpQKf70VP4gqT-wrjWjL29j-SjlOtwI

Enjoy!

LC

www.finance-bi.com

View solution in original post

lc_finance · ‎10-15-2019

Here is the new formula, I took the time to simplify it:

RepeatCustomerDifferentDays New = 


SUMX(VALUES('Datatable'[OutletCode]),
    VAR dates =  CALCULATETABLE(VALUES('Datatable'[OrderDate]))
    RETURN IF(COUNTX(dates, [OrderDate])>1,1,0)

)

View solution in original post

morab · ‎10-16-2019

Hi @lc_finance

View solution in original post

lc_finance · ‎01-27-2020

Hi @morab ,

You can find my solution attached:

https://finance-bi.com/wp-content/uploads/2020/01/customer-transactions-by-month.zip

Here is how it works:

Outlet No = 

var numberOfMonths = SELECTEDVALUE('Number of Months'[number of month])
var outlets = VALUES('Table1'[OutletCode])
var purchaseByMonth = SELECTCOLUMNS(outlets,
"outlets", [OutletCode],
"months with purchase", 
    SUMX(VALUES('Calendar'[Year Month Number]), 
    var countTransactions = CALCULATE(COUNTROWS('Table1'))
    RETURN IF(countTransactions>0,1,0)
    )
)

var purchasedXMonths = COUNTX(FILTER(purchaseByMonth, [months with purchase]=numberOfMonths), [months with purchase])

RETURN purchasedXMonths

The variable number OfMonths is equal to the number of month selected (for example: purchase in only 1 month, purchase in 2 months, etc)

The variable outlets has a list of all the outlets

The variable purchaseByMonth is a table with all the outlets and a column specifying on how many months the outlet bought the product

Finally, the variable purchasedXMonth counts the number of outlets that bought for a specified number of months (based on the variable number of months).

Does this help you?

Regards

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

View solution in original post