Hi , I am having a data like YearTypeSoldCostPrice2020T150060011002020T22005007002020T35004009002020T480030011002020T5110020013002020T65001006002020T713001302021T150060011002021T22005007002021T35004009002021T480030011002021T5110020013002021T65001006002021T713001302022T150060011002022T22005007002022T35004009002022T480030011002022T5110020013002022T65001006002022T71300130 I want to Transform intoYearTypeSoldCostPrice2020T150060011002020T22005007002020T35004009002020T480030011002020T5110020013002020T65001006002020T71300130Total 3730210058302021T150060011002021T22005007002021T35004009002021T480030011002021T5110020013002021T65001006002021T71300130Total 3730210058302022T150060011002022T22005007002022T35004009002022T480030011002022T5110020013002022T65001006002022T71300130Total 373021005830I want to add a total for other column based on each year ..Is this posible Thanks ..

Ayappan

Helper III

4 years ago

Solved

Want to organize my data using Transform Data

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 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: