Customer Churn Analysis sales compare with sales of Non-Member customers


Dear Sam,

In your case scenario (Customer Churn), you assumed each order related to a registered customer (has a reference number in the sales table), The Majority of real business cases the customer reference (customer no) as a foreign key might be present or could be Null (The order not referenced to a registered customer) .
Based on this what sort of changes we should do to calculate :slight_smile:

1-Total sales for customers(members) and the (Non-membered) customers who purchased?
2-The percentage of sales for new customers against Sales of (members & Non-membered) customers?

If you can please support your answer with DAX expression.

Thank You


I would need an example with data, but not sure you can run customer churn analysis when you don’t have data registering what customer is buying what.


Ok Here is the ER Diagram for my case :slight_smile:

This ER is common everywhere the customer table Have relationship (1:m) with Orders(e.g.: Sales) table.

Here is an example of the Entry Of Orders table:

As you see some customers accept from the store to keep saving their Information (foreign key will be entered in the customer_hid column inside the orders table for membership) , If you found records are Null means the customer has no reference registry in the store it means they (customers) don’t accept to keep their information in the store (they don’t want a membership), this is the whole story which is common everywhere.

The customer churn works successfully with me but the problem is the Total Sales measure calculating only the sales with customers who have the membership based on the membership domain, but I need to compare the sales of non-members with the ones who have a membership dynamically. This problem happened particularly with Visuals need comparisons between the two domains (with/without membership) e.g. Stacked column bar:

The measures I use for new(lost) customer is exactly 100% the same as what you did but only the naming is a deference. All What I need is the (Total Sales) measure doing adaptation between the 2 domains of customers with/without membership.

Is the case example have enough information Sam?


This is a good explanation, but I don’t believe it really assists as I’m still unsure how you plan to calculated customer churn when there is no customer data or customer records for a lot of the data.

So you’re looking to compare sales with customer vs non customers.

Have you broken out these two calculations using FILTER?

For example…

CALCULATE( Total Sales,
FILTER( Customer Table, Total Sales > 0 )

Something like this I think. Or based on this sort of logic.


Simply I use visualisation filter to show me only the rows which have the customer column with reference , so as you suggest I’ll do a total sales particularly for those whom have the reference as mentioned and then I’ll get back to you if there is a problem BTW (customer vs non customers).


Ok Sam It goes in progress I create a new measure to calculate only the members but it has some small problem:

Total Member Customer Sales =
CALCULATE([Total Sales],
FILTER(Customers,[Total Sales]>0))

I did some editing to the expression like this
Total Member Customer Sales =
CALCULATE([Total Sales],
FILTER(ALL(Customers),[Total Sales]>0))

Until Now It works fine , but Just for double checking I’ll get back to you if there are some problems.