Forum Discussion
Summarize a summarize
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
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 _BOutput:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
2 Replies
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 _BOutput:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Thank you, this was a good idea 🙂
