Calculate costs across years from multiple columns

[Data_boi]

Hello,

I have a table where the year column represents the claim year, paid years to represent the amount they were paid that year, estimate year is the amount they were estimated to be paid that year and returned year is the amount they paid back that year;

ID	Year	Paid 2019	Paid 2020	Paid 2021	Estimate 2019	Estimate 2020	Estimate 2021	Returned 2019	Returned 2020	Returned 2021
123	2018	100	150	150	50	50	50	0	50	50
124	2019	0	150	150	0	200	200	0	100	100
125	2020	0	0	50	0	0	0	0	0	50

The total claims amount per year is the Paid + Estimate - Returned.

What I want is to be able to plot the claim per year based on this calculation similar to this;

Year	Paid	Estimate	Recovered	Claim amount
2019	100	50	0	150
2020	300	250	150	400
2021	350	250	200	400

My first thought was to create a new table with all the paid, estimated and recovered values in which I could then pivot, however, as the actual table ranges around 25 years with each year having 3 columns I thought a SELECTCOLUMNS() statement would be ridiculously large.

Is there another way I can do it or is this the only way? 

TYIA

[amitchandak · Accepted solution]

@Data_boi , Unpivot the first table and then split the column with the column name based on space into two column

https://radacad.com/pivot-and-unpivot-with-power-bi
Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g

Split into new columns

Power Query Split Column , Split Column By Delimiter: https://youtu.be/FyO9Vmhcfag

[Data_boi]

Thank you I'll give this a try!