Summarize a summarize

[rynhowl]

How do I do this SQL query in DAX? I've searched the forums; I don't know if I'm just searching the wrong terms or what, but I can't find anything that works.

SELECT

    AVG(SumOfMaxGrades) AS ClassAverageGrade

FROM (

    SELECT

        Student,

        SUM(MaxGrade) AS SumOfMaxGrades

    FROM (

        SELECT

            Student,

            Assignment,

            MAX(Grade) AS MaxGrade

        FROM TableA

        GROUP BY Student, Assignment

    ) B

    GROUP BY Student

) C

Additional context if needed: In the example, I have students who get mutliple attempts on each assignment and only the max score is kept for each one, then their final grade is the sum of all assignment grades, and I want to find the class average. (Please don't try to correct my grading logic here, this is a made up example, the actual dataset has nothing to do with grading.)

Student	Assignment	Grade
Bob	1	90
Bob	1	95
Bob	2	85
Bob	2	87
Sally	1	92
Sally	1	88
Sally	2	70
Sally	2	75
John	1	45
John	1	50
John	1	65
John	2	65
John	2	70

Student	Assignment	Max Grade
Bob	1	95
Bob	2	87
Sally	1	92
Sally	2	75
John	1	65
John	2	70

Student	Sum of assignment grades
Bob	182
Sally	167
John	135

Class Average Grade: 161.33

[VahidDM · Accepted solution]

Hi @rynhowl 

Try this measure:

Class Average Grade =
VAR _A =
    SUMMARIZE (
        'Table',
        'Table'[Student],
        'Table'[Assignment],
        "Max Grade", MAX ( 'Table'[Grade] )
    )
VAR _B =
    SUMX ( _A, [Max Grade] ) / COUNTROWS ( VALUES ( 'Table'[Student] ) )
RETURN
    _B

Output:

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

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[rynhowl]

Thank you, this was a good idea 🙂