Forum Discussion
Sudhir_M1992
3 years agoFrequent Visitor
YoY Retention%
Please help me with DAX to calculate YoY Retention% one basis of email I'ds.
Mrxiang
3 years agoHelper II
RetentionRate =
VAR TotalCustomers = SUM(CASE WHEN Name IS NOT NULL AND Email IS NOT NULL THEN 1 ELSE 0 END)
VAR UniqueCustomers = DISTINCTCOUNT(Name)
VAR TotalEmails = SUM(CASE WHEN Name IS NOT NULL AND Email IS NOT NULL THEN Email ELSE NULL END)
VAR UniqueEmails = DISTINCTCOUNT(Email)
RETURN
IF(TotalCustomers > 0,
IF(TotalCustomers > UniqueCustomers,
RetentionRate = (TotalCustomers - UniqueCustomers) / UniqueCustomers * 100,
0)
)
This measure calculates the total number of customers, unique customers, and total emails for each year. It then computes the retention rate by dividing the number of unique customers by the total number of customers, and adjusts the result if the number of customers is less than the number of unique customers. The result is then returned as a percentage.
Note that this measure assumes that each customer is unique, which may not be the case in your data. If some customers have the same email address, they will be counted as the same customer in the calculation of unique customers. If this is not the case, you may need to adjust the measure accordingly.Sudhir_M1992
3 years agoFrequent Visitor
Hey Mrxiang Thanks for your comment.
If possible, Could you please add this measure in PBIX file as I have already shared the sample data?
- Mrxiang3 years agoHelper II
I didn't see the PBIX file you shared.Can you send me a demo?