Forum Discussion
Kudo KingpinCount Active Customers based on Average Monthly transactions
I have a regular star schema sales model.
I need to build a measure that counts how many active customers we have in any selected period.
The Active Customer calculation is when a customer makes on Average more than 5 purchases monthly.
so if a customer buys 10 purchases on January, nothing on Feb and 2 on March, then his/hers average is 12/3=4, not active.
I am looking for SUMX solution, not DIVIDE , since my next measure will be how many sales are there for these customers.
I understand I need to prepare virtual table of customer and month, but when I try SUMMIRIZE it does not include empty month where there were no purchases per customer. I need it to treat them as zero.
please help
3 Replies
- amitchandak
Super User
michaelsh , refer to these two blogs if they can help
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retention-is-only/ba-p/2114497
michaelshI went through these and these are not relevant for my case.
I am looking for the MONTHLY AVERAGE calculation per customer, it the above posts you are showing how to calculate per some period, not MONTHLY in a chosen period.
Think if a user choses Quarter 1, I need to calculate per customer per each of the three months: Jan, Feb, March, even if there were no sales!
I feel that I need crossjoin between customers and months,
Please help
Hi michaelsh ,
Please provide some data and expected output.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
