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

Postby jw95 » Mon Sep 23, 2019 12:17 pm

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

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

Re: Chapter 14 - Repeat Customers

Postby RamanaV » Mon Sep 23, 2019 4:09 pm

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
2019-09-23_160633.png (5.32 KiB) Viewed 427 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

Postby jw95 » Fri Sep 27, 2019 10:22 am

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

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

Re: Chapter 14 - Repeat Customers

Postby RamanaV » Fri Sep 27, 2019 11:24 am

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

Postby jw95 » Mon Sep 30, 2019 9:37 am

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


Return to “Help with the Book "Supercharge Power BI"”

Who is online

Users browsing this forum: No registered users and 1 guest

cron