Calculate and visualize student progression (throughput) rate by year.

[MagubaneSSP]

Hi Power Bi Experts,

I am a newbie to power bi and need help with the following analysis on student data:

1. Throughput or progression rate by year, i.e. students moving from 1 'programme level' to the next (level 1 - level 2 - level 3, and D1 - D2 - D3)?

My source data has the following columns:

• Year (2016, 2017, 2018, 2019, 2020)
• Student ID (unique IDs)
• Gender (Female, Male)
• Region (DC, OH)
• Qualification (Marketing, Finance)
• Programme (Entry, Advanced)
• Programme level (level 1, level 2, level 3, D1, D2, D3)
• Completion Status (Complete and Incomplete)

I have been able to calculate % completed by year, programme level, and qualification using this measure: % Completed = [Total Completed] / [Total Students], but struggling to show progression %, i.e. calculating the number and percentage of students who complete their first qualification and register for the next higher level of the qualification.

Hope my questions are clear.

Thanking you all in advance. 

[v-xiaotang]

Hi @MagubaneSSP 

so you want to calculate the number and percentage of students who complete their first qualification and register for the next higher level of the qualification?

could you show us the result you want (You can give examples in Excel/PBI file after removing sensitive information, or you can give us hand drawn data)?

FYI: https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1579748#M6420...

Best Regards,

Community Support Team _ Tang

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

[MagubaneSSP]

Hi,

Thank you for taking the time to review my questions.

To this end, I am attaching a sample PBIX file and source reflecting the data I want to analyse as follows:

https://we.tl/t-CAaZuiK9Vs 

(NB!I have been able to calculate the first 6 measures, but please also share yours so I can compare if I am on the correct path).

1. Distinct count of students by programme and year.

2. Distinct count of students qualification and year.

3. Distinct count of students by programme level and year.

4. Distinct count of students by gender (I would like to visualize this as a percentage of the total students).

5. Distinct count of new (first time registration), and returning (registered before) students.

6. Forecast enrolment from 2021 to 2030, factoring new and returning students (I am thinking I could use the 'Analytics' option in power bi?)

The most difficult analysis for me is:

1. Number and percentage of students completing their qualifications by year.

2. Number and percentage completing by year and programme

3. Number and percentage completing by year and qualification

4. Number and percentage completing by year and programme level.

5. Number and percentage of students completing their qualification and they enroling for a higher qualification (i.e. percentage and number of students moving from programme level 'V1 to V2, and to V3', and programme level N1 to N2, and to N3).

6. And finally, a calculated slicer (or normal slicer) that will show me the number and percentage of students completing their qualification within 1 year, 2 years, 3 years, etc.

Outputs

1. I just want to be able to visualize this analysis in a matrix and  line charts to show trend or differences for the years under review.

2. For the progression analysis, I am hoping to show something like the table below (if it is at all possible):

Programme Level	Year 0	Year 1	Year 2	Year 3	Year 4	Year 5
V1
V2
V3
N1
N2
N3

Or something like this which I can filter by year:

Programme Level	Number of New Students	Number of Returning Student	Total Number of Students Enrolled	Number of Returning Students Repeating the Same Level	Number of Returning Students Progressing to Next Level	Percentage of Returning Students Moving to Next Level
V1
V2
V3
D1
D2
D3

Apologies if this is too much information, I just wanted give insights on the type of analysis I want to do with the data I have.

Please let me know if you have additional questions.

Thanking you all in advance!

[v-xiaotang]

Hi @MagubaneSSP 

I would like to help you, but when I open link it does not show your PBI file.
without specific data, I can only give you simple example,

-
"1. Number and percentage of students completing their qualifications by year."
e.g. count1=calculate(count('table'[students]),filter(all('table'),'table'[students]="input your filter content"&& your filter2 && your filter3))

"2. Number and percentage completing by year and programme"
filter by year or programme, there are many similar posts and you can find it quickly.

-

Generally speaking, the logic of your other questions is the same, you can draw inferences from one another.

FYI:

https://docs.microsoft.com/en-us/dax/

Best Regards,

Community Support Team _ Tang

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

[amitchandak]

@MagubaneSSP ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.