Chapter 14 - Repeat Customers

This is a dedicated forum for people that have questions about the book Supercharge Power BI. In the event of errors in the book, the error information will be in this forum.
jw95
Posts: 6
Joined: Mon May 13, 2019 4:51 pm

Chapter 14 - Repeat Customers

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
Attachments
Annotation 2019-09-23 101731.png (19.15 KiB) Viewed 706 times

RamanaV
Posts: 53
Joined: Thu Oct 19, 2017 12:57 pm

Re: Chapter 14 - Repeat Customers

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 701 times

Ramana
Ramana Varanasi
Training and Office Manager
Excelerator BI Pty. Ltd.

jw95
Posts: 6
Joined: Mon May 13, 2019 4:51 pm

Re: Chapter 14 - Repeat Customers

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
Attachments
Annotation 2019-09-27 082141.png (22.05 KiB) Viewed 643 times

RamanaV
Posts: 53
Joined: Thu Oct 19, 2017 12:57 pm

Re: Chapter 14 - Repeat Customers

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
Ramana Varanasi
Training and Office Manager
Excelerator BI Pty. Ltd.

jw95
Posts: 6
Joined: Mon May 13, 2019 4:51 pm

Re: Chapter 14 - Repeat Customers

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