Retention Rate by level of purchase

[AH2022]

Hi,

I calculate Retention RateY = DIVIDE([ConstantClientCount],[ActiveClientCountYearAgo]) 

and display it by different criteria (Departments, Region etc)

The client request is to calculate the Retention Rateby by the level of purchase value per customer. Example High ( 25k plus), Medium (5k-10k), Low (5k and less).

I added a calculated column in the customer table, which gives the level of each customer by overall average value for the respective customer.

The problem is that in real life, a customer can buy one year for 5k, and the next year 10k and then 25k. Therefore, will move yearly between levels: Year 1 could be low, year 2 could be medium and year 3 could be high.

The method I used will count the client, all the time, within the same category of purchase determined by the average of all purchases for this client.

I need to display the Retention Rate, 5 years evolution, witch consider this client, in different categories of purchase ( if 2020, $25k, high),( if 2021, $2k, low )

Thanks for the advice or link or any other reference.

[AH2022]

I used this method in order to use the measure as a legend : 

Measure As Legend Power BI Real World Cases and Solutions TAIK18 (14-25) Power BI - YouTube

DAX Measure in the Axis of the Power BI Report - RADACAD

[Anonymous · Accepted solution]

Hi @AH2022 ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a measure. 

Level = 
var _a = CALCULATE(SUM('Table'[price]),FILTER(ALLSELECTED('Table'),YEAR('Table'[date])=YEAR(MAX('Table'[date]))))
return 
SWITCH(TRUE(),
_a<5000,"Low",
_a>=5000 && _a<10000,"Medium",
_a>25000,"High")

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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