Forum Discussion

CL7777's avatar
CL7777
Helper III
6 years ago
Solved

Counting repeat customers

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 ...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    6 years ago

    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
PaulDBrown's avatar
PaulDBrown
Community Champion
6 years ago

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's avatar
    CL7777
    Helper III
    6 years ago

    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's avatar
      PaulDBrown
      Community Champion
      6 years ago

      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