Forum Discussion

lukeSDM's avatar
lukeSDM
Icon for Helper V rankHelper 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 , ...
Need Help
BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper 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
    Icon for Helper V rankHelper 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
      Icon for Super User rankSuper 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
        Icon for Helper V rankHelper V
        5 years ago

        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