Retention analysis

[Harpreet1405_12]

Dear team,

I am working on calculting the retention between semesters per program, the data is organized in the different format so I am finding it difficult to caluclate the retention. Here is the screenshot of the data ( just one program data)

In this image,dates are their audit date ( when i group my data by audit date , then it is giving me right caluclation ), this is the visualization , the data is organized in raw format where each enrolment is listed as one row ( there are 20,000 rows per student_id).

1. This image shows the enrolment numbers semester -wise (1,2,3,4,5,6,7) and so on as this program has 7 semesters

2. the retention should be caluclated semester wise (1-2,2-3,3-4 and so on) if we read first row : 97( nov 2011), 69(march 2012) and so on 

3. there are some of the rows which have null values so we have to consider that as well

can you please let me know how should i achieve this in power BI (any feedback would be highly appreciated)

[TomMartens]

Hey @Harpreet1405_12 ,

I have difficulties understanding what data looks like and what you want to achieve. For this reason, I recommend that you prepare a pbix file that contains sample data but reflects your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix to OneDrive, Google Drive, or Dropbox and share the link. If you do not use the manual input method for the sample data, make sure that the sample data is available in a spreadsheet and share this spreadsheet as well.

Do not forget to explain the expected result based on the sample data.

Regards,

Tom

[Harpreet1405_12]

https://docs.google.com/spreadsheets/d/1rEm88TNk8pwqW8lZEsoKuTBvzdWyXG8D/edit?usp=sharing&ouid=11777...

Here is the link of the spreadsheet which has a sample data set i am working on. The enrolment data grouped by Audit date make more sense and it will give you the output which is listed in above screenshot ( In this screenshot i transpose the semester data into column and then count the distinct unique id grouped together by audit date). I want to achieve something like this: 

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[Harpreet1405_12]

Hello Ashish,

This might be a silly question, but i am trying to understand how the retention is working here as I would like to calculate the semester wise retention grouped by year : as this course has 4 semesters ( i do not know how i am anayzing the retention here)

[Ashish_Mathur]

As you progress through each column of a row, you will see the retnetion percentage of that year.

[Harpreet1405_12]

Hello Ashish,

Can we track by semester ( i mean semester 1 , semester 2, semester 3, and so on)? and also listed by Year. something like this :

Instead of months

[Ashish_Mathur]

Yes, you can.  Please study my solution and adapt it to your needs.

[Harpreet1405_12]

Hello Ashish,

I apply the formula which you gave me for retention analysis , but my output is giving me the sum instead of year by year retention , this is how my output looks like [ also i am trying to use this formula for the multiple programs [ please let me know what i am doing wrong here]

I appreciate all your help

Thank u

[Ashish_Mathur]

Hi,

In my screenshot, you can clearly see %.  In your screenshots, i see an absolute figure.  So you are obviously missing the division operation.

[Harpreet1405_12]

The division operation in my case is working like this :

I used the exact formula that you used

[Ashish_Mathur]

Recheck the relationships.

[Harpreet1405_12]

This is great Ashish, it works but i am looking more of a semester wise retention for example : for this particular example : the retention should be calculated like 26 - 26 (100%), then 26- 24( 92%), then 24-23( 95%), is it possible to achieve that and update this following table with semester wise retention across the semester ( please provide your thoughts)

[Harpreet1405_12]

The retention of unique id from one semetesr to 2 and 2nd to 3rd an dthen 3rd to 4th etc. how many people we loose from start to completion of the module. Does it make sense?

[Harpreet1405_12]

This is great, thank you so much for your response