Forum Discussion

lukeSDM's avatar
lukeSDM
Helper V
5 years ago

Calculating the difference between different rows with a common subject

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

 

 

StudentResult SetAspectGrade value
JimTargetCAG - Maths3
JeffTargetCAG - Maths4
JimAutumnCAG - Maths2
JeffAutumnCAG - Maths3
JimSpringCAG - Maths4
JeffSpringCAG - Maths4
JimTargetCAG - English7
JeffTargetCAG - English9
JimAutumnCAG - English4
JeffAutumnCAG - English6
JimSpringCAG - English7
JeffSpringCAG - English2
Need Help

18 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    5 years ago

    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

  • lukeSDM's avatar
    lukeSDM
    Helper V
    5 years ago

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

    For Example:

    StudentEnglish: Target-Autumn DifferenceEnglish : Autumn to Spring DifferenceEnglish : Target - Spring DifferenceMaths: Target-Autumn DifferenceMaths: Target - Spring Difference
    Jim+1-1+2+5-4
    Jeff+2+4000

     

    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's avatar
      BA_Pete
      Super User
      5 years ago

      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's avatar
        lukeSDM
        Helper V
        5 years ago

        Thank you for this I have another question.

         

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

         

        StudentResult SetAspectGrade value
        JimTargetTarget Maths3
        JeffTargetTarget Maths4
        JimAutumnCAG - Maths2
        JeffAutumnCAG - Maths3
        JimSpringCAG - Maths4
        JeffSpringCAG - Maths4
        JimTargetTarget English7
        JeffTargetTarget English9
        JimAutumnCAG - English4
        JeffAutumnCAG - English6
        JimSpringCAG - English7
        JeffSpringCAG - English2

         

        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.

  • lukeSDM's avatar
    lukeSDM
    Helper V
    5 years ago

     

    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.

     

    nameResult setAspect Grade
    JimYear 11 FFT Reported GradeKS4 FFT New Eng2
    JimYear 11 FFT Reported GradeKS4 FFT New Maths2
    JimYear 11 FFT Reported GradeKS4 FFT New Maths3
    JimYear 11 FFT Reported GradeKS4 FFT New Eng4
    JimYear 11 FFT Reported GradeKS4 FFT New Eng3
    JimYear 11 FFT Reported GradeKS4 FFT New Maths2
    JimYear 11 Autumn TermTeacher Assessment Maths New2
    JimYear 11 Autumn TermTeacher Assessment Eng New3
    JimYear 10 Autumn TermTeacher Assessment Eng New3
    JimYear 10 Autumn TermTeacher Assessment Maths New1
    JimYear 10 Spring TermTeacher Assessment Maths New1
    JimYear 10 Spring TermTeacher Assessment Eng New4
    JimYear 10 Summer TermTeacher Assessment Maths New1
    JimYear 10 Summer TermTeacher Assessment Eng New4
    JimYear 10 Interim Autumn TermTeacher Assessment Maths New1
    JimYear 10 Interim Autumn TermTeacher Assessment Eng New3
    JeffYear 11 FFT Reported GradeKS4 FFT New Maths2
    JeffYear 11 FFT Reported GradeKS4 FFT New Eng3
    JeffYear 10 FFT Reported GradeKS4 FFT New Eng3
    JeffYear 10 FFT Reported GradeKS4 FFT New Maths3
    JeffYear 10 Autumn TermTeacher Assessment Eng New3
    JeffYear 10 Autumn TermTeacher Assessment Maths New1

     

     

    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.

     

     

  • BA_Pete's avatar
    BA_Pete
    Super User
    5 years ago

    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's avatar
      lukeSDM
      Helper V
      5 years ago

      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's avatar
        BA_Pete
        Super User
        5 years ago

        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's avatar
    lukeSDM
    Helper V
    5 years ago

    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

  • lukeSDM's avatar
    lukeSDM
    Helper V
    5 years ago

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

  • lukeSDM's avatar
    lukeSDM
    Helper V
    5 years ago

    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's avatar
    lukeSDM
    Helper V
    5 years ago

    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