Counting repeat customers

[CL7777]

HEllo Forum,

I am an intermediate dax user and Im struggling with this task

I want to count repeat customers in a table, keeping all filters applied in the visual.

Example file

customer      country          part number          year

AA1                  USA              BB-1                     2016

AA1                  USA              BB-1                     2017

AA1                  USA              BB-1                     2018

AA2                  Canada         BB-2                     2016

AA2                  Canada         BB-2                     2017

AA3                  USA              BB-2                     2016

AA3                  USA              BB-2                     2018

I want to create a measure that works with filters to count repeat customers.

In the above table, with no filters, this should return the number 3 as there are 3 customers who came back and purchased the same part number (AA1, AA2, and AA3).

If the visual filters for country USA, then this measure should return 2 as 2 customers in the USA came back and purchased the same part number (AA1 and AA3)

If the visual filters for part number BB-2, then this measure should return 2 as 2 customers who bought a BB-2 came back and purchased the same part number (AA2 and AA3)

Im sure it is easier than I am making it out to be, but I cant get the right filter context to produce the correct result. Any help would be greatly appreciated.

[Icey]

Hi @CL7777 ,

I have tested @PaulDBrown 's latest DAX expressions and they work well. If you have no other questions, please accept his reply as a solution so that people who may have the same question can get the solution directly. 

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown]

@CL7777 

Try this:

(In my examples, I've named your sample table 'Customer Data'):

1) Calculate number of years per customer:
 

Distinctcount years = CALCULATE(DISTINCTCOUNT('Customer data'[year]); 
                       ALLEXCEPT('Customer data'; 'Customer data'[Customer]))

2) Count the number of customers who have bought in at least 2 years:

Repeating Customers = CALCULATE(
                      DISTINCTCOUNT('Customer data'[Customer]); 
                       FILTER('Customer data';
                         [Distinctcount years]>1)) + 0

(NB the + 0 at the end is to avoid seeing "BLANK()" as a result)

See if that works for you.

[CL7777]

Thanks for your repsonse. This is not exactly working because it calculates the number of repeat customers, but not if I were to filter by product for example. The first measure you wrote calculates the number of years that each customer purchased something but it would need to count the number of years that each customer purchased a particular part. When I filter by part in the visual, it doesnt work.

[PaulDBrown · Accepted solution]

@CL7777 

I see what you mean. Tricky this one, but I think I've cracked it.

Give this a try:

1) First measure: 

Distinctcount years = CALCULATE(DISTINCTCOUNT('Customer data'[year]); 
                      ALLEXCEPT('Customer data'; 
                      'Customer data'[Customer]; 'Customer data'[Country];'Customer data'[part number]))

2) Final Measure to deliver repeating customers:

Repeating Customers = CALCULATE(DISTINCTCOUNT('Customer data'[Customer]);
                       FILTER(ALL('Customer data'[Customer]);
                        [Distinctcount years]>1)) +0 

[Anonymous]

Hi,

I am not sure if this would help but from my understanding you can

1.select write a DAX expression as Distinctcount(customer)

2.select a multirow KPI card add measure from step1 and add customer field too!

you will be able to see the names of customers and the counts that they appear after your filters. Thanks