Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## 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 Android JP user who installed App A through Facebook network, campaign_1, Image_1 during 2020-01-01~2020-01-02

- Retention Rate (D+1) = (50+140)/(100+200)

Retention Rate for iOS JP user who installed App A through Google network, campaign_2, Video_1 during 2020-01-01~2020-01-02

- Retention Rate (D+1) = (30+40)/(50+100)

* Retention Rate (D+n) = retained_users with period n / retained_users with period 0

 App OS Country Network Campaign Ad Group Creative Date Period Retained_users A Android JP Facebook campaign_1 Image Image_1 2020-01-01 0 100 A Android JP Facebook campaign_1 Image Image_1 2020-01-01 1 50 A Android JP Facebook campaign_1 Image Image_1 2020-01-02 0 200 A Android JP Facebook campaign_1 Image Image_1 2020-01-02 1 140 A iOS JP Google campaign_2 Video Video_1 2020-01-01 0 50 A iOS JP Google campaign_2 Video Video_1 2020-01-01 1 30 A iOS JP Google campaign_2 Video Video_1 2020-01-02 0 100 A iOS JP Google campaign_2 Video Video_1 2020-01-02 1 40

I want to show retention rate by variables (App, OS, Country, Network, Campaign, Ad Group, Creative, Date).

How can I solve this problem ?

1 ACCEPTED SOLUTION
Super User

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.

5 REPLIES 5
Super User

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

Regular Visitor

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

 0 4,103 3,674 3,858 4,015 4,391 5,985 4,420 5,390 5,801 2,905 2,880 3,722 3,421 3,789 4,948 2,885 1 2,575 1,917 2,072 2,729 3,067 3,803 2,794 4,169 4,221 2,013 1,993 2,535 2,518 3,598 3,505 2 1,404 961 1,207 1,324 1,560 2,095 1,593 1,648 1,939 969 1,014 1,759 1,190 1,204 3 1,307 967 1,048 1,414 2,042 2,375 1,963 1,674 1,662 1,247 1,126 1,369 1,276 4 846 680 957 1,396 1,605 1,891 1,334 1,513 1,964 924 899 1,198 5 780 1,033 1,186 1,117 1,257 1,449 948 1,199 1,206 739 689 6 1,063 987 838 876 1,044 1,173 944 879 1,000 560 TOTAL 46,798 39,759 42,528 42,541 52,166 63,271 49,469 56,152 59,594 34,984 34,898 49,548 41,968 43,666 58,532 36,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%
Regular Visitor

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

Super User

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.

Regular Visitor

@lbendlin lbendlin

What I am struggling with is how to get the cummulative totals. (See examples from Excel)

I have spent a week wracking my brain on the caluclation to perform this, but I haven't been succesful.

Period 3 Example

Period 6 Example

I was trying to do a date diff based on the max date where the subscriber count wasn't blank, but I can't get it accross the finish line. I would GREATLY appreciate anyone who could help

## Helpful resources

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors