Want to organize my data using Transform Data

[Ayappan]

Hi ,

    I am having a data like 

Year	Type	Sold	Cost	Price
2020	T1	500	600	1100
2020	T2	200	500	700
2020	T3	500	400	900
2020	T4	800	300	1100
2020	T5	1100	200	1300
2020	T6	500	100	600
2020	T7	130	0	130
2021	T1	500	600	1100
2021	T2	200	500	700
2021	T3	500	400	900
2021	T4	800	300	1100
2021	T5	1100	200	1300
2021	T6	500	100	600
2021	T7	130	0	130
2022	T1	500	600	1100
2022	T2	200	500	700
2022	T3	500	400	900
2022	T4	800	300	1100
2022	T5	1100	200	1300
2022	T6	500	100	600
2022	T7	130	0	130

I want to Transform into

Year	Type	Sold	Cost	Price
2020	T1	500	600	1100
2020	T2	200	500	700
2020	T3	500	400	900
2020	T4	800	300	1100
2020	T5	1100	200	1300
2020	T6	500	100	600
2020	T7	130	0	130
Total		3730	2100	5830
2021	T1	500	600	1100
2021	T2	200	500	700
2021	T3	500	400	900
2021	T4	800	300	1100
2021	T5	1100	200	1300
2021	T6	500	100	600
2021	T7	130	0	130
Total		3730	2100	5830
2022	T1	500	600	1100
2022	T2	200	500	700
2022	T3	500	400	900
2022	T4	800	300	1100
2022	T5	1100	200	1300
2022	T6	500	100	600
2022	T7	130	0	130
Total		3730	2100	5830

I want to add a total for other column based on each year ..

Is this posible 

Thanks ..

[Ayappan]

Thank for your reply @SpartaBI 

Yes i want to create a caclulated table with the new structure?

[Ayappan]

@SpartaBI Please help me out 

[SpartaBI]

@AyappanIf you put your data as it is in a matrix you get it straightforawrd (attached):
Totals.pbix
Or you mean you want to create a caclulated table with the new structure?

Showcase Report – Contoso By SpartaBI

      

[Ayappan]

Thanks @SpartaBI for the response 

I want to create a caclulated table with the new structure..

[SpartaBI · Accepted solution]

@Ayappan not sure why you would want to do that 🙂 but I created a dax for this table (looks almost like what you want):

Table With Totals = 
VAR _data = 'Table'
VAR _totlas = 
	ADDCOLUMNS(
		VALUES('Table'[Year]),
		"Type", "Total",
		"Sold", CALCULATE(SUM('Table'[Sold])),
		"Cost", CALCULATE(SUM('Table'[Cost])),
		"Price", CALCULATE(SUM('Table'[Price]))
	)
VAR _union = 
	UNION(
		_data,
		_totlas
	)

VAR _result_with_sorting = 
	ADDCOLUMNS(
		_union,
		"Sorting", 'Table'[Year] & RIGHT([Type])
	)
RETURN
	_result_with_sorting

You need to sort the table in the model by the Sorting column like in the photo:

This is the updated file with my solution:
Totals.pbix

Showcase Report – Contoso By SpartaBI