topic Re: Calculating how a students grade in a subject compares to their average grade. in DAX Commands and Tips

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

pconridge — Mon, 15 Jan 2024 12:41:56 GMT

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

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

amustafa — Tue, 16 Jan 2024 03:23:11 GMT

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()

)

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

pconridge — Tue, 16 Jan 2024 19:37:08 GMT

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",

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

amustafa — Wed, 17 Jan 2024 02:20:30 GMT

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

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

pconridge — Sun, 21 Jan 2024 18:24:54 GMT

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?

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

amustafa — Mon, 22 Jan 2024 10:10:44 GMT

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()

)

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

pconridge — Tue, 06 Feb 2024 09:37:15 GMT

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