Forum Discussion
Calculating the difference between different rows with a common subject
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
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.
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