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 , ...
Need Help
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.

      • BA_Pete's avatar
        BA_Pete
        Super User
        5 years ago

        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