Calculating the difference between different rows with a common subject

[lukeSDM]

Hi All,

I am looking to calculate the difference between the grades , between the result sets for each aspect.

So for 'Jim' I want to calculate the difference between his target and in autumn , and then target and spring and the inbetween (so autumn to spring) if possible for both maths and english.

I would really appreciate any help as I am not sure how to tackle this.

This is how the data looks - would I need to pivot it or?

Like I say any help appreciated.

Hopw this makes sense.

Many thanks,

Luke

Student	Result Set	Aspect	Grade value
Jim	Target	CAG - Maths	3
Jeff	Target	CAG - Maths	4
Jim	Autumn	CAG - Maths	2
Jeff	Autumn	CAG - Maths	3
Jim	Spring	CAG - Maths	4
Jeff	Spring	CAG - Maths	4
Jim	Target	CAG - English	7
Jeff	Target	CAG - English	9
Jim	Autumn	CAG - English	4
Jeff	Autumn	CAG - English	6
Jim	Spring	CAG - English	7
Jeff	Spring	CAG - English	2

[lukeSDM]

I am having to send a picture of the better example table as for some reason the error is with html is in the table, so sorry but i cannot help that.

please find the other picture i mentioned aswell

[lukeSDM]

Hi  

I have been attempting to get your solution to work and it does, to some extent , however when I look at the results it produces they are wrong however when giving you example data i did not incorporate other factors in and i think this is causing an issues.

Please let me give some data that reflects my data source better and see if it makes sense and you could get this to work(I will only use one student as an example and I will include the steps you have alreadty explained to me. I did adapt some of the formula to work with my data a bit better):

The data continues in a very similar pattern for all years with different names and some other changes.

from the example data in the tabel you would expect the autumn variable difference to be -1 as the calculation would do 2 (actual grade) - 3 (target grade) to give them -1 to show they are below target.

however after following your steps but tweaking them to try and fit my datat better I gte the answer of -3 which I am confused on how it would have gotten this answer.

Any help is really appreciated, I feel like it is so close to being resolved.

Thanking you so much in advance,

Luke,

(I have also attached a picture of the data to help with understanding)

Thanks again.

[lukeSDM]

I cannot post my full reply and as their is HTML issues can anyway help.

[lukeSDM]

Hi @BA_Pete ,

I am trying to implement this into my own PBI document but I am confused on how to do this, I have followed your steps for the powerquery part and its come up with the example data but how do i make this work for my real data?

Do i have to copy and paste a bit of your code onto my datas source advanced editor?

Many thanks,

Luke

[BA_Pete]

Hi @lukeSDM ,

This new data example makes it a bit more complicated to implement tidily. A couple of questions:

- Are you able to change the data source at all? Adding separate columns for Grade Year, Term, and Subject would make things simpler.

- If not, are you able to use Power Query (i.e. are you using Import mode for your data)? If you are using import mode and can't add the fields to your source as per the question above, then I can show you how to do this in Power Query.

Pete

[lukeSDM]

Hi @BA_Pete ,

I am unable to change the data source unfortunatley, the example data I shared is exported from a school information management system directly to an csv file and I then connect to this csv file within Power Bi.

So yes, I believe that I can do bits in Power Query. 

I look forward to your response.

Thank you very much fro your help, it is greatly appreciated.

[BA_Pete]

Hi @lukeSDM ,

Apologies for the delay.

The more I look at your new scenario, the more confused I get.

Can you clear up the following please?

- Is the 'Teacher Assessment" aspect a target or an actual value?

- Jim, for example has three KS4 FFT New Eng values in year 11, but none of them show which term (Spring, Summer, Autmun) they apply to. How can i distinguish which Target to compare to which Actual?

Thanks,

Pete

[lukeSDM]

Hi @BA_Pete 

I would like to compare Jims "KS4 FFT New Eng" (which is an aspect) , result set "FFT Reported Grade" (which is the time of when the grade was achieved) "result" with Jims "Teacher Assessmnet Eng New (Aspect) , "Year 11 Autumn" (result set) result.

So essentially I want to compare the grade from2 diffrent aspects with 2 result set.

I hopw that clears things up.

Many thanks

[BA_Pete]

Ok, but Jim has three KS4 grades, with no way of distinguishing between them, except they have different grade values. Should these be summed/averaged?

Pete

[lukeSDM]

My apologies that is a mistake within the data I provided, there should only be one "FFT Reported Grade" which is the target.

I hope this clears things up.

Many thanks @BA_Pete 

[BA_Pete]

That makes waaaay more sense!

I've done this in two parts to simplify the measures.

1) Split out dimensions in Power Query.

In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZNNDoIwEIWv0nTNApALsBCjRhfSjSEsGhmERSuZlnh9i5j4QxWBsJ3p99502pckdFMK6tAjcCSeR6KIkQNUF9SQkRXyDExzGwf3xh6uZCnPpuLT1BmM7rgulKktLHBY61pIwgCbKgN+KgBJqBQoJUDqFm5kvrj3CpjBH3jX350dfx3fswjEFZbGYUaB5wUCG14LYc5P8O8T+O2/lhqwFNMX+bdQ50Ehz8f8Zn8U3cbI4uzOy35k8J2etrPhKUhv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, #"Result set" = _t, Aspect = _t, Grade = _t]),
    chgSourceTypes = Table.TransformColumnTypes(Source,{{"name", type text}, {"Result set", type text}, {"Aspect", type text}, {"Grade", Int64.Type}}),
    dupeResultSet = Table.DuplicateColumn(chgSourceTypes, "Result set", "Result set - Copy"),
    splitResultSetDupe = Table.SplitColumn(dupeResultSet, "Result set - Copy", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"gradeYear", "gradePeriod"}),
    trimGradePeriod = Table.TransformColumns(splitResultSetDupe,{{"gradePeriod", Text.Trim, type text}}),
    addGradeType = Table.AddColumn(trimGradePeriod, "gradeType", each if Text.Contains([Aspect], "Assessment") then "Target" else "Actual", type text),
    addSubject = Table.AddColumn(addGradeType, "Subject", each if Text.Contains([Aspect], "Eng") then "English" else if Text.Contains([Aspect], "Maths") then "Maths" else "No subject", type text)
in
    addSubject

2) Create variance measures as before.

_varianceSpring = 
VAR grade =
CALCULATE(
    MAX(lukeSDMTable2[Grade]),
    lukeSDMTable2[gradeType] = "Actual"
)
VAR target =
CALCULATE(
    MAX(lukeSDMTable2[Grade]),
    lukeSDMTable2[gradePeriod] = "Spring Term"
)
RETURN
grade - target

_varianceSummer = 
VAR grade =
CALCULATE(
    MAX(lukeSDMTable2[Grade]),
    lukeSDMTable2[gradeType] = "Actual"
)
VAR target =
CALCULATE(
    MAX(lukeSDMTable2[Grade]),
    lukeSDMTable2[gradePeriod] = "Summer Term"
)
RETURN
grade - target

_varianceAutumn = 
VAR grade =
CALCULATE(
    MAX(lukeSDMTable2[Grade]),
    lukeSDMTable2[gradeType] = "Actual"
)
VAR target =
CALCULATE(
    MAX(lukeSDMTable2[Grade]),
    lukeSDMTable2[gradePeriod] = "Autumn Term"
)
RETURN
grade - target

You can then apply your dimensions/measures to a matrix like this:

The main thing to note is, that in order to keep the measures as dynamic as possible, I've added an extra dimension to the matrix i.e. [gradeYear]. You could take this out of the matrix and add it as a slicer if you prefer, but it will need to be applied as a filter somewhere as it's not hardcoded into the measures.

Pete

[lukeSDM]

@BA_Pete 

This looks great , I will give it a try and let you know my result.

Thank you for all your help so far.

[lukeSDM]

Hi @BA_Pete I do not quite understand your explination, sorry I am not very experienced in this.

Would you be able to give me examples of what you have asked me to do.

I have attached an example of my data to help give you a better understanding.

So I would like to compare [Aspect] "KS4 FFT New Eng" [Result set] Year 11 FFT Reported Grade" [Grade] with [Aspect] "Teacher Assessment Eng New" [Result set] "Year 11 Autumn Term" [Grade].

I hope this makes sense and I look forward to your response.

Thank you for all your help so far.

name	Result set	Aspect	Grade
Jim	Year 11 FFT Reported Grade	KS4 FFT New Eng	2
Jim	Year 11 FFT Reported Grade	KS4 FFT New Maths	2
Jim	Year 11 FFT Reported Grade	KS4 FFT New Maths	3
Jim	Year 11 FFT Reported Grade	KS4 FFT New Eng	4
Jim	Year 11 FFT Reported Grade	KS4 FFT New Eng	3
Jim	Year 11 FFT Reported Grade	KS4 FFT New Maths	2
Jim	Year 11 Autumn Term	Teacher Assessment Maths New	2
Jim	Year 11 Autumn Term	Teacher Assessment Eng New	3
Jim	Year 10 Autumn Term	Teacher Assessment Eng New	3
Jim	Year 10 Autumn Term	Teacher Assessment Maths New	1
Jim	Year 10 Spring Term	Teacher Assessment Maths New	1
Jim	Year 10 Spring Term	Teacher Assessment Eng New	4
Jim	Year 10 Summer Term	Teacher Assessment Maths New	1
Jim	Year 10 Summer Term	Teacher Assessment Eng New	4
Jim	Year 10 Interim Autumn Term	Teacher Assessment Maths New	1
Jim	Year 10 Interim Autumn Term	Teacher Assessment Eng New	3
Jeff	Year 11 FFT Reported Grade	KS4 FFT New Maths	2
Jeff	Year 11 FFT Reported Grade	KS4 FFT New Eng	3
Jeff	Year 10 FFT Reported Grade	KS4 FFT New Eng	3
Jeff	Year 10 FFT Reported Grade	KS4 FFT New Maths	3
Jeff	Year 10 Autumn Term	Teacher Assessment Eng New	3
Jeff	Year 10 Autumn Term	Teacher Assessment Maths New	1

This is currently how i have my data in Power Bi which is not good :

(You can disregard the Attitude Aspect as Your previous solution will work for this)

Thank you again.

[lukeSDM]

Hi @BA_Pete I want to have a matix that has a column for the difference for each subject. 

For Example:

Student	English: Target-Autumn Difference	English : Autumn to Spring Difference	English : Target - Spring Difference	Maths: Target-Autumn Difference	Maths: Target - Spring Difference
Jim	+1	-1	+2	+5	-4
Jeff	+2	+4	0	0	0

I woulf then like to use conditional formtting on the values so its green if there is positive progress , amber if it stays the same and red if there is negative progress.

Hope this makes sense and thank you very much for your help!

[BA_Pete]

@lukeSDM ,

Thanks for the update.

Set up these measures:

_varianceAutumnSpring = 
VAR autumn =
CALCULATE(
    MAX(lukeSDMTable[Grade value]),
    lukeSDMTable[Result Set] = "Autumn"
)
VAR spring =
CALCULATE(
    MAX(lukeSDMTable[Grade value]),
    lukeSDMTable[Result Set] = "Spring"
)
RETURN
spring - autumn

_varianceTargetAutumn = 
VAR autumn =
CALCULATE(
    MAX(lukeSDMTable[Grade value]),
    lukeSDMTable[Result Set] = "Autumn"
)
VAR target =
CALCULATE(
    MAX(lukeSDMTable[Grade value]),
    lukeSDMTable[Result Set] = "Target"
)
RETURN
autumn - target

_varianceTargetSpring = 
VAR spring =
CALCULATE(
    MAX(lukeSDMTable[Grade value]),
    lukeSDMTable[Result Set] = "Spring"
)
VAR target =
CALCULATE(
    MAX(lukeSDMTable[Grade value]),
    lukeSDMTable[Result Set] = "Target"
)
RETURN
spring - target

Then you can add them to a matrix visual with your dimensions like this:

To get the following output based on the example data provided:

Pete

[lukeSDM]

Thank you for this I have another question.

Is there a way to get this to work if the aspects had different names?

Student	Result Set	Aspect	Grade value
Jim	Target	Target Maths	3
Jeff	Target	Target Maths	4
Jim	Autumn	CAG - Maths	2
Jeff	Autumn	CAG - Maths	3
Jim	Spring	CAG - Maths	4
Jeff	Spring	CAG - Maths	4
Jim	Target	Target English	7
Jeff	Target	Target English	9
Jim	Autumn	CAG - English	4
Jeff	Autumn	CAG - English	6
Jim	Spring	CAG - English	7
Jeff	Spring	CAG - English	2

I would like it to work exactly as you have described before but I want to compare "Target Maths" to "CAG - Maths".

Do you know of a way to do this @BA_Pete .

I thank you very much for your help so far.

[BA_Pete]

Hi @lukeSDM ,

It is possible, but it would require hard-coding all of the different combinations of [CAG - Subject] vs [Target - Subject]. You would essentially use the same measure structure as before but substituting out the combinations in the variable (VAR) sections.

If this is a data source issue, i.e. all of your target/CAG info is in the [Aspect] field in the source and you don't actually have a [Result] field, then I would recommend using the Split Column function in Power Query on the [Aspect] field to create your own [Result] field then follow the original method.

Pete

[BA_Pete]

Hi @lukeSDM ,

Could you provide a basic example of what you want your end result to be please?

What type of visualisations are you going to use, will it always only be Spring and Autumn or Winter and Summer too, are the seasons/terms always compared to a single target value or can you have seasonal targets, can you use Power Query, are you using Direct Query?

Pete