Substracting in matrix

[igalav2021]

Hello all,

I'm new to this community and hope you can assist with something I'm struggling with. 

After filtering exam dates (see table below), I want to be able to show the difference between the scores of each exam and each section, so then I can filter only the sections in which there was a change in score. 

I tried to follow some ideas that were shared here before, but couldn't find something that I fully understand and that worked. Can someone please assist? Thank you!!

[bcdobbs]

Are you able to share a demo PBIX file with your basic table structure and some dummy data. This is by far the easiest to work with.

Alternaively a picture of your data model and example of data in each table

[igalav2021]

Thanks for the swift response. 

I understand that the screenshot is not sufficient? 

I can't share the PBIX (corporates confidential information), but created a dummy data sheet. let me know if this helps. 

Student ID	Exam Date	Exam Number	Section	Score
111111	27/11/2020	15	1	20
111111	27/11/2020	15	2	60
111111	27/11/2020	15	3	20
111111	27/11/2020	16	1	15
111111	27/11/2020	16	2	45
111111	27/11/2020	16	3	0
222222	27/11/2020	15	1	10
222222	27/11/2020	15	2	50
222222	27/11/2020	15	3	25
222222	27/11/2020	16	1	20
222222	27/11/2020	16	2	45
222222	27/11/2020	16	3	15
111111	03/12/2021	15	1	0
111111	03/12/2021	15	2	50
111111	03/12/2021	15	3	20
111111	03/12/2021	16	1	15
111111	03/12/2021	16	2	60
111111	03/12/2021	16	3	25
222222	03/12/2021	15	1	10
222222	03/12/2021	15	2	70
222222	03/12/2021	15	3	20
222222	03/12/2021	16	1	20
222222	03/12/2021	16	2	45
222222	03/12/2021	16	3	30

I hope this is clearer now.

[bcdobbs]

Great. And you're looking at differences for each student?

[igalav2021]

for each student, and for each exam section. I want to flag only the sections that had score changes. (comparing the same exam number and sections with 2 different dates, flaging the sections with score changes). 

Just to highlight that the dates are filtered. there are more test dates with more values.

[igalav2021]

Just to highlight that the dates are filtered. there are more test dates with more values.

[bcdobbs]

Using your data and column names importing it as a table called Result I first wrote this measure:

Score Comparison = 
    VAR EarliestDate = FIRSTDATE( Result[Exam Date] )
    VAR LatestDate = LASTDATE( Result[Exam Date] )

    VAR EarliestScore = 
        CALCULATE(
            SUM(Result[Score]),
            EarliestDate
        )
    
    VAR LatestScore = 
        CALCULATE(
            SUM(Result[Score]),
            LatestDate
        )

    RETURN 
    IF(
        HASONEVALUE(Result[Exam Date]), 
        SUM(Result[Score]),
        LatestScore - EarliestScore
    )

Then put that measure into a matrix:

Lastly if you go into the subtotal section of the matrix formating you can change "Total" to read "Difference"

[igalav2021]

Unfortunately, it doesn't work. 

It gives back 0 as the results for all the rows: 

[bcdobbs]

Few questions:

In your version do you just have a single table like the data you sent?
Are Weight and Weighted Score just extra columns?
Presumably you've modified the dax I sent to match your structure. Can your modified version back and I'll see if I can spot the issue.

[igalav2021]

Thank you for your support!! I double-checked and indeed I made a mistake 🙂 

There are indeed more columns that I didn't mention. Basically, each exam section has a different weight. 

Your solution works. However, the outcome shows the score comparison column for each date and the Difference (the subtotal) shows the calculation for all columns and not only for the Raw Score. I want to avoid showing unnecessary columns that might confuse the user. Any thoughts? 

[bcdobbs]

There are other options where you could use two disconnect tables from which to select dates but the basic idea remains the same.