Creating difference columns in Matrix

[EpicTriffid]

Hi all,

Just to demonstrate what I need I have created these in Excel:

Bit of a weird one, and might not be possible, but I have the following matrix viz showing scores based on subject by year. The Year can be classed as an actual (it's already happened), or a forecast (targets for the future):

The data is stored as below:

Table 1

I would to do the following in the matrix mentioned above, with columns for differences between certain forecast years and the latest actual year:

So, the 1 Year Forecast difference would minus 2024 from 2023 and return the difference, the 5 Year Forecast difference would minus 2028 from 2023 and return the difference and so on. The sum itself isn't rocket science, but it's whether i can put that all in one matrix like the above.

Is this at all possible? I've tried creating a calculated column in my table that calculcates scores for 2028 and scores for 2023 (subtracting one row from another) and minuses the former from the latter, (in order to just get the maths sorted out) but my matrix just looks like this instead:

Can anyone help? I'm totally stumped! I need those difference columns, if not as part of the matrix itself then at least as calculations on their own. Thank you!

[v-yadongf-msft]

Hi @EpicTriffid ,

Has your problem been solved? If solved, please consider Accept it as the solution to help the other members find it more quickly.

Best regards,

Yadong Fang

[mangaus1111 · Accepted solution]

Hi @EpicTriffid ,

you can use such solution putting one matrix above the other.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.