Help with merit value and school data

Hi Anonymous ,

For Q1:

The 17 grade merit value =
VAR _t =
    SUMMARIZE (
        FILTER ( ALL ( studentgrade ), [studentID] = MAX ( 'studentgrade'[studentID] ) ),
        [gradesubject]
    )
VAR _countGrades =
    CALCULATE (
        DISTINCTCOUNT ( 'studentgrade'[gradesubject] ),
        ALLEXCEPT ( studentgrade, studentgrade[studentID] )
    )
RETURN
    IF (
        _countGrades = 17
            && "M2"
                IN _t
                    && CALCULATE (
                        MAX ( 'studentgrade'[gradevalue] ),
                        FILTER (
                            'studentgrade',
                            [studentID] = MAX ( 'studentgrade'[studentID] )
                                && [gradesubject] = "M2"
                        )
                    ) >= 10,
        CALCULATE (
            SUM ( 'studentgrade'[gradevalue] ),
            ALLEXCEPT ( studentgrade, studentgrade[studentID] )
        ),
        IF (
            _countGrades > 17
                && "M2" IN _t,
            CALCULATE (
                SUM ( 'studentgrade'[gradevalue] ),
                ALLEXCEPT ( studentgrade, studentgrade[studentID] )
            )
                - CALCULATE (
                    MIN ( 'studentgrade'[gradevalue] ),
                    FILTER (
                        'studentgrade',
                        [studentID] = MAX ( 'studentgrade'[studentID] )
                            && [gradesubject] <> "M2"
                    )
                )
        )
    )

The 16 grade merit value =
VAR _t =
    SUMMARIZE (
        FILTER ( ALL ( studentgrade ), [studentID] = MAX ( 'studentgrade'[studentID] ) ),
        [gradesubject]
    )
VAR _countGrades =
    CALCULATE (
        DISTINCTCOUNT ( 'studentgrade'[gradesubject] ),
        ALLEXCEPT ( studentgrade, studentgrade[studentID] )
    )
RETURN
    IF (
        _countGrades = 17
            && NOT ( "M2" IN _t ),
        CALCULATE (
            SUM ( 'studentgrade'[gradevalue] ),
            ALLEXCEPT ( studentgrade, studentgrade[studentID] )
        )
            - CALCULATE (
                MIN ( 'studentgrade'[gradevalue] ),
                FILTER (
                    ALL ( 'studentgrade' ),
                    [studentID] = MAX ( 'studentgrade'[studentID] )
                )
            ),
        IF (
            _countGrades <= 17
                && NOT ( "M2" IN _t ),
            CALCULATE (
                SUM ( 'studentgrade'[gradevalue] ),
                ALLEXCEPT ( studentgrade, studentgrade[studentID] )
            )
        )
    )

For Q2:

Percentage =
VAR _t =
    SUMMARIZE (
        FILTER ( 'studentgrade', [finalgrade] = 1 ),
        [studentID],
        "Flag", IF ( MIN ( studentgrade[gradevalue] ) >= 10, 1, 0 )
    )
VAR _Numerator =
    SUMX ( _t, [Flag] )
RETURN
    _Numerator
        / CALCULATE (
            DISTINCTCOUNT ( studentgrade[studentID] ),
            FILTER ( ALL ( 'studentgrade' ), [finalgrade] = 1 )
        )

Outputs:

For Q3, it seems that there is no field to specify the program types(vocational / Art, Music and Drama Business Management and Economics program / Humanities program and the Social Science / Natural science program and Technology)

In addition, how about the more X subjects, are they random?

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

Hi Anonymous  

First, I must thank you for taking the time to help me. I am very grateful for that!

Q1

The calculations you've made works but there are no value for student 2 and 3. They should have a value for the 16 grade merit value.

I also don't seem to get the merit value per class or school. Can I use the measures to create new measures to get the values per class?

Q2

I don't get the percentage to work per class or school.

What I was looking for the total amount of students that failed no subjects.

In class 9A, two students (1 and 2) passed all subjects. Student 1 had 18 grades and student 2 had 16 grades. There are four students in class 9A which means that 50% of the students passed all subjects

Q3

I haven't specified the program types because I don't know how to do it. Is it best to specify in its own table or is it better to specify if a student is eligible for each program type in columns in the students table, with a column for each program type?

About the more X subjects, they are random.

For each program type, there are a number of MUST-subject and a number of EXTRA-subjects. The student must have passed at least all the MUST- and EXTRA-subjects to be eligible for the program type.

The student that is eligible for the Natural science program and Technology program, is by default also at least eligible for the vocational and the Art, Music and Drama program.

Reading your feedback has made me realize that I wasn't as clear in my descriptions or in my data as I had hoped ‌‌ 

I've added data in the excel file

Data added for 4 additional students in the studentgrades and students tables to get data for Q3.
Results tab added, with manually calculated answers to my questions (Q1-Q3)

Again, a big thank you for taking the time!

Kind regards, 

Stina