Forum Discussion
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 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.
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
5 Replies
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.
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
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.
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.
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
