Hi Power-Bi Experts,
I am circling back on this question, and hope I am clear enough to get a solution this time.
I would like to calculate the student completion rate by:
- Period (Trimester 1, Trimester 2, Trimester 3)
- Qualification (Marketing, Finance)
- Qualification Level (Level 1, Level 2, Level 3)
Background of qualifications provided, and completion criteria:
- Each qualification level has 4 subjects. For example, we have 4 subjects for Marketing Level 1, 4 subjects for Marketing Level 2, and 4 subjects for Marketing Level 3. The same structure applies to the Finance qualifications. In essence, this means we have 6 qualifications (3 Marketing and 3 Finance qualifications).
- Duration of all qualifications is defined by trimesters (12/13 weeks), and there are 3 trimesters in 1 year.
- A student cannot be registered for 2 qualifications at a time or within 1 trimester. For example, a student cannot be registered for Marketing Level 1 and Marketing Level 2 in Trimester 1.
- Completion of qualification. A student is deemed to have completed a qualification once they pass all 4 subjects for a particular qualification. This means a student may pass, e.g., 2 subjects in trimester 1, and re-register to complete the outstanding subjects in trimester 2/3. This also means a student can complete up to 3 qualifications in 1 year.
My dataset is a bit limited, and I have the following columns on an excel spreadsheet:
- Student Unique ID
- Gender
- Race
- Period (Trimester 1, Trimester 2, Trimester 3)
- Qualification (Marketing, Finance)
- Qualification level (Level 1, Level 2, Level 3)
- Subject
- Campus
- College
The absence of complete data (pass/fail column) makes this a bit tricky to analyze. My thinking however is that one can use the student ID period, qualification, and qualification level information to create a measure/calculation to identify students that have completed their qualifications. For example, if we have records of Student A registered for Marketing Level 1 in Trimester 1, and we also have records of Student A registered for Marketing Level 2 in Trimester 2, then student A completed Marketing Level 1.
Is this possible to achieve, or there is a better approach?
Any guidance will be appreciated.
Thank you.
