Calculating how a students grade in a subject compares to their average grade.

[pconridge]

Hi,

I have pasted at the bottom of this post a table of student assessment results.

I would be very grateful for help to create a DAX measure that calculates how a student's grade in a particular subject (aspect) compares to that students average grade in the other subjects they have a result for.

I want teachers to be able to see how a student's performance in their subject is different compared to that students performance in their other subjects.  I'd also like to be able to show an aggregated residual for subjects in a seperate table.

For example the table below shows what i am trying to achieve.

This student has an average Year 11 Predicted grade 1 value of 4.36  

This has been subtracted from their grade for each subject to give the residual column.

Student Id	Resultset	Grade value	Aspect	Residual
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	6	KS4 Art WL18-25	1.64
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	5	KS4 Design Tech WL 19-25	0.64
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	5	KS4 English Lang WL 17-25	0.64
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	5	KS4 English Lit WL 17-25	0.64
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	5	KS4 French WL18-24	0.64
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	5	KS4 Geography WL18-25	0.64
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	6	KS4 Maths WL 17-25	1.64
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	6	KS4 Religious Stud WL18-25	1.64
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	6	KS4 Science Trilogy WL18-25	1.64
		4.36

ChatGpt suggested the following DAX however as the screenshot for one particular student shows this is producing the wrong answer.

Residual =
VAR SelectedStudent = SELECTEDVALUE(AssessmentY11[Student])

VAR AvgGrade =
CALCULATE(
AVERAGE(AssessmentY11[Grade Value]),
ALL(AssessmentY11),
AssessmentY11[Student] = SelectedStudent,
AssessmentY11[Aspect] IN VALUES(AssessmentY11[Aspect]),
AssessmentY11[Result Set] = "Year 11 Predicted 1"
)

RETURN
IF(
NOT ISBLANK(AvgGrade),
AVERAGE(AssessmentY11[Grade Value]) - AvgGrade,
BLANK()
)

Any help much appreciated.

Thanks

Phil

I need to restrict the aspects being considered to the following:

AssessmentY11[aspect] IN { "KS4 Art WL18-24","KS4 Art WL18-25", "KS4 Biology WL18-25","KS4 BTEC Art and Design WL 24",

"KS4 BTEC Sport WL 24-25","KS4 BTEC Tech DIT WL 24-25","KS4 BTEC Vocational WL 23-25","KS4 BTEC Workskills WL 23-25",

"KS4 Business Studies WL 19-25","KS4 Chemistry WL18-25","KS4 Computer Science WL 20-25","KS4 Design Tech WL 19-25",

"KS4 Drama WL18-25","KS4 Engineering Award WL 24-25","KS4 English Lang WL 17-25","KS4 English Lit WL 17-25","KS4 Food and Nutrition WL 21-25","KS4 French WL18-24","KS4 Geography WL18-25","KS4 History WL18-25","KS4 Hospitality Award WL 20-23",

"KS4 Hospitality Award WL 24-25","KS4 Maths WL 17-25","KS4 Media Studies WL 19-25","KS4 Music WL18-25","KS4 Physics WL18-25","KS4 Religious Stud WL18-25","KS4 Science Trilogy WL18-25","KS4 Spanish WL 18-25","KS4 SPE WL18-25"

Here are the results for two pupils

External Id	Resultset	Grade value	Aspect
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	6	KS4 Art WL18-25
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	6	KS4 Religious Stud WL18-25
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	5	KS4 English Lang WL 17-25
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	5	KS4 English Lit WL 17-25
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	6	KS4 Maths WL 17-25
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	5	KS4 Design Tech WL 19-25
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	6	KS4 Science Trilogy WL18-25
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	1	Difference Art PG tp target
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	-1	Difference English Lan PGTG
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	1	Difference Religious StPGTG
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	1	Difference FrenchPGTG
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	-1	Difference English Lit PGTG
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	5	KS4 French WL18-24
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	5	KS4 Geography WL18-25
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	0	Diff DT PG to Expected
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	0	Diff RS PG to Expected
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	0	Diff EngLit PG to Expected
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	0	Diff Geog PG to Expected
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	0	Diff Maths PG to Expected
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	0	Diff French PG to Expected
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	2	Difference Science Tril PG TG
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	0	Diff Scien Tril PG to Expected
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	0	Diff Art PG to Expected
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	0	Diff EngLan PG to Expected
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	0	Difference MathsPGTG
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	0	Difference GeographyPGTG
000c75c0-5dfb-4e8c-a2b0-a50a4dd341c2	Year 11 Predicted 1	1	Difference DT Product PG TG
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	8	KS4 Religious Stud WL18-25
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	9	KS4 Biology WL18-25
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	9	KS4 Chemistry WL18-25
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	7	KS4 English Lang WL 17-25
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	7	KS4 English Lit WL 17-25
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	8	KS4 Maths WL 17-25
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	1	Difference Computer ScPGTG
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	0	Difference English Lan PGTG
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	2	Difference Religious StPGTG
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	1	Difference PhysicsPGTG
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	2	Difference FrenchPGTG
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	0	Difference English Lit PGTG
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	8	KS4 French WL18-24
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	7	KS4 Music WL18-25
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	7	KS4 Physics WL18-25
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	7	KS4 Computer Science WL 20-25
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	1	Difference Music PG TG
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	0	Diff Physics PG to Expected
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	0	Diff RS PG to Expected
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	0	Diff French PG to Expected
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	0	Diff EngLit PG to Expected
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	0	Diff Maths PG to Expected
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	0	Diff Music PG to Expected
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	0	Diff Biology PG to Expected
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	0	Diff Chemistry PG to Expected
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	0	Diff Comp Studies PG to Expected
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	0	Diff EngLan PG to Expected
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	3	Difference ChemistryPGTG
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	1	Difference MathsPGTG
00e8c871-07cf-4144-8d56-a77cf84d3d5f	Year 11 Predicted 1	3	Difference Biology PG to Target
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	1.75	KS4 BTEC Vocational WL 23-25
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	2	KS4 Religious Stud WL18-25
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	1	KS4 English Lang WL 17-25
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	1	KS4 English Lit WL 17-25
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	2	KS4 Maths WL 17-25
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	3	KS4 Science Trilogy WL18-25
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	-1	Difference English Lan PGTG
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	-1	Difference Religious StPGTG
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	0	Diff BTEC Voc PG to Expected
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	-1	Difference English Lit PGTG
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	1	KS4 Geography WL18-25
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	0	Diff RS PG to Expected
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	1.75	KS4 BTEC Workskills WL 23-25
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	0	Diff EngLit PG to Expected
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	0	Diff Geog PG to Expected
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	0	Diff Maths PG to Expected
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	0	Diff BTEC WrkSklls PG to Expected
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	0	Diff Hospitality PG to Expected
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	1	Difference Science Tril PG TG
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	0	Difference BTEC Voc PG TG
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	0	Difference BTEC Workskil PGTG
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	0	Diff Scien Tril PG to Expected
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	0	Diff EngLan PG to Expected
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	0	Difference MathsPGTG
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	0	KS4 Hospitality Award WL 24-25
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	-1	Difference GeographyPGTG
04214cde-aec9-40a2-b27a-a69098ee74c3	Year 11 Predicted 1	-3	Difference Hospitality PG to TG

[pconridge · Accepted solution]

Thank you once again @amustafa 

Your residuals do not quite calculate correctly.

As you can see the average grade of the student is 5.44.  In English for example 5-5.44 is -0.44 not the -0.5 calculated.  I cant work out how to adapt your code to get the correct value.  Any ideas?

[amustafa · Accepted solution]

@pconridge Thank you for clarifying how you want to calculate the Risidual value. Here's the updated DAX. You can download the pbix file form my shared folder as well.

Residual =

VAR SelectedStudent = SELECTEDVALUE(AssessmentY11[Student])

VAR CurrentAspect = SELECTEDVALUE(AssessmentY11[Aspect])

VAR OverallAvgGrade =

    CALCULATE(

        AVERAGE(AssessmentY11[Grade Value]),

        FILTER(

            ALL(AssessmentY11),

            AssessmentY11[Student] = SelectedStudent &&

            AssessmentY11[Result Set] = "Year 11 Predicted 1"

        )

    )

VAR GradeCurrentSubject =

    CALCULATE(

        AVERAGE(AssessmentY11[Grade Value]),

        FILTER(

            ALL(AssessmentY11),

            AssessmentY11[Student] = SelectedStudent &&

            AssessmentY11[Aspect] = CurrentAspect &&

            AssessmentY11[Result Set] = "Year 11 Predicted 1"

        )

    )

RETURN

IF(

    NOT ISBLANK(GradeCurrentSubject) && NOT ISBLANK(OverallAvgGrade),

    GradeCurrentSubject - OverallAvgGrade,

    BLANK()

)

[amustafa]

try...

Residual =

VAR SelectedStudent = SELECTEDVALUE(AssessmentY11[Student])

VAR CurrentAspect = SELECTEDVALUE(AssessmentY11[Aspect])

VAR AvgGradeOtherSubjects =

    CALCULATE(

        AVERAGE(AssessmentY11[Grade Value]),

        FILTER(

            ALL(AssessmentY11),

            AssessmentY11[Student] = SelectedStudent &&

            AssessmentY11[Aspect] <> CurrentAspect &&

            AssessmentY11[Result Set] = "Year 11 Predicted 1"

        )

    )

VAR GradeCurrentSubject =

    CALCULATE(

        AVERAGE(AssessmentY11[Grade Value]),

        FILTER(

            ALL(AssessmentY11),

            AssessmentY11[Student] = SelectedStudent &&

            AssessmentY11[Aspect] = CurrentAspect &&

            AssessmentY11[Result Set] = "Year 11 Predicted 1"

        )

    )

RETURN

IF(

    NOT ISBLANK(GradeCurrentSubject),

    GradeCurrentSubject - AvgGradeOtherSubjects,

    BLANK()

)

[pconridge]

Thank you @amustafa foryour help.  Much appreciated.  Unfortunately the measure doesnt quite work as can be seen below. 

This might be because there are other aspects up against the year 11 predicted 1 result set.  Is it possible to adapt your DAX to only perform the residual calculation where the aspect is one of the following list? 

AssessmentY11[aspect] IN { "KS4 Art WL18-24","KS4 Art WL18-25", "KS4 Biology WL18-25","KS4 BTEC Art and Design WL 24","KS4 BTEC Sport WL 24-25","KS4 BTEC Tech DIT WL 24-25","KS4 BTEC Vocational WL 23-25","KS4 BTEC Workskills WL 23-25","KS4 Business Studies WL 19-25","KS4 Chemistry WL18-25","KS4 Computer Science WL 20-25","KS4 Design Tech WL 19-25","KS4 Drama WL18-25","KS4 Engineering Award WL 24-25","KS4 English Lang WL 17-25","KS4 English Lit WL 17-25","KS4 Food and Nutrition WL 21-25","KS4 French WL18-24","KS4 Geography WL18-25","KS4 History WL18-25","KS4 Hospitality Award WL 20-23",

"KS4 Hospitality Award WL 24-25","KS4 Maths WL 17-25","KS4 Media Studies WL 19-25","KS4 Music WL18-25","KS4 Physics WL18-25","KS4 Religious Stud WL18-25","KS4 Science Trilogy WL18-25","KS4 Spanish WL 18-25","KS4 SPE WL18-25"

[amustafa]

Hi @pconridge ,

I did use the data you provided and filter the results but I have different Residual values. See my file sin my shared drive.

AssessmentY11

[pconridge · Accepted solution]

Thank you once again @amustafa 

Your residuals do not quite calculate correctly.

As you can see the average grade of the student is 5.44.  In English for example 5-5.44 is -0.44 not the -0.5 calculated.  I cant work out how to adapt your code to get the correct value.  Any ideas?

[pconridge]

Thank you so much for your help with this @amustafa   You are a superstar.  I am really grateful.