Chapter 14 - Repeat Customers

[jw95]

Hi all,

I like to find out how many repeat customers I have in this database. How do you go about it?

I did a DISTINCTCOUNT on [CustomerKey] in the Sales table, the values seems to be the same COUNT on {CustomerKey] in Customers table (See attached). Can I infer that there is no repeat customer?

Assuming that there are repeat customers, how do i find out who are the repeat customers and how many are there?

Many thanks,
Jeffrey

[RamanaV]

Hi,

Calculate the no. of distinct invoices first
Distinct No. of Invoices = DISTINCTCOUNT(Sales[SalesOrderNumber])

Now put a Table visual in your report and place Customers[CustomerKey] on Rows and [Distinct No. of Invoices] on Columns
You will then see how many invoices each customer has.

Next, you can filter the Table to have only repeated customers.
Go to Filters pane and place a filter Distinct No. of Invoices is greater than 1.

2019-09-23_160633.png (5.32 KiB) Viewed 1407 times

Ramana

[jw95]

Hi Ramana,

Thanks for responding. I am able to replicate what you suggested.

So I continue reading and come to Chapter 16, RELATED and RELATEDTABLE. The example in the book gave me an idea of calculating the no of sales by customer key, to try to answer what I asked earlier, thinking that there are many ways to skin a cat. BUT the numbers are different (see attached). Am I right to say that the Sales table is structured in a way that is based on items line (or ProductKey), and not by SalesOrderNumber, therefore the difference in results?

Cheers,
Jeffrey

[RamanaV]

Hi,

Good to know that you are able to understand and proceed.

To get a good understanding on how DAX formulas work, it would be useful to observe the data keenly.

If you look at Sales table, there is a column - SalesOrderNumber, which is essentially an invoice
Now, one invoice will have one OrderDate, one CustomerKey, but can be 1 or more ProductKeys based on what products are included in that single purchase

So if you take COUNTROWS you are essentially counting the products purchased by the customer.
If you want to know how many times the customer purchased you need to take the distinct no. of purchases and it is distinct count of SalesOrder numbers.

You can extend this to check how many products a customer purchased, how many products the customer purchased more than once, ... Just expand your imagination and thinking.

And, in the second table above, did you use any filters? I got a total of 60,598

Ramana

[jw95]

Hi Ramana,

You are right. I did use filter of more than 1. Using my approach of RELATEDTABLE, I am not able to distinct count specific column such as SalesOrderNo as RELATEDTABLE only allows me to choose table, not column.

Now, I am in particular curious to know how would you approach to solve " How many products the customer purchased more than once", as this question involved 3 variables - CustomerKey, ProductKey and SalesOrderNo.

Best regards,
Jeffrey