Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Cohort analysis - retention rate

Hi, I'm new to Power BI and I want to calculate and show retention rate on dashboard. I have tables regarding user cohort data and we calculate retention rate as below.   ex. Retention Rate for A...
cohort
DAX
Retention
  • lbendlin's avatar
    lbendlin
    5 years ago

    You cannot show the retention rate by date.  Remove the [date] column from your table visual.

     

     

    Then it is easy to create the measure. For each "row" of the table visual identify if the period is 0. If yes, skip the row (return BLANK() or 1). If no, create a variable to calculate the sum of Retained_users , replacing the Period filter with 0. Lastly divide the sum of Retained_users for the current "row" by the calculated variable.

     

     

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
5 years ago

Please provide sample data in usable format (not as a picture) and show the expected outcome.

Johann1978's avatar
Johann1978
Regular Visitor
5 years ago

Thank you in advance for your time and guidance..I am more than happy to attach a file the info below isn't clear. 

The formula for the Output is below

Month 3 Sum( Month active Column: rows 0-3 Jan-Jan 2021)/Total Column :Jan-Mar 2021

Month 5 Sum (Month Active Column: rows 0-5 Jan-Nov)/Total Column :Jan-Nov

 

DATA TABLE

04,1033,6743,8584,0154,3915,9854,4205,3905,8012,9052,8803,7223,4213,7894,9482,885
12,5751,9172,0722,7293,0673,8032,7944,1694,2212,0131,9932,5352,5183,5983,505 
21,4049611,2071,3241,5602,0951,5931,6481,9399691,0141,7591,1901,204  
31,3079671,0481,4142,0422,3751,9631,6741,6621,2471,1261,3691,276   
48466809571,3961,6051,8911,3341,5131,9649248991,198    
57801,0331,1861,1171,2571,4499481,1991,206739689     
61,0639878388761,0441,1739448791,000560      
TOTAL46,79839,75942,52842,54152,16663,27149,46956,15259,59434,98434,89849,54841,96843,66658,53236,388
OUTPUT                
 Attrition               
Same Month (0)        8.8%               
M+1     14.9%               
M+2     17.9%               
M+3     21.0%               
M+4     23.7%               
M+5     26.2%               
M+6     28.2%               
  • Johann1978's avatar
    Johann1978
    Regular Visitor
    5 years ago

    Sorry for the view above it trimmed out the month headers on the columns when it converted it. I am more than happy to send an email if you have time