How to create summarized new table out o existing one?

[Shiji_Menon]

From the following table

I wanted to create the following visual in 'table' in power bi tile.

I tried to create a new table named SummaryTable and following is the query used.

SummaryTable =
UNION(
ROW("Activity", "2023", "2022" ),
ROW("Total Passengers",[Psngr_Cnt_2023] ,[Psngr_Cnt_2022] ),
ROW("Total Flight Departed", [Departure_Cnt_2023], [Departure_Cnt_2023])
)

But it was not working and showing error "here there was a error Column '2022' added by the 'ROW' function must have the scalar expression specified."

Following are the formulas used

Psngr_Cnt_2022 = CALCULATE(SUM(Table1[Passengers Total]),DATESYTD(ENDOFYEAR(dateadd(Table1[Year ],-1,Year)),"12/31"))
Psngr_Cnt_2023 = CALCULATE(SUM(Table1[Passengers Total]),DATESYTD(ENDOFYEAR(Table1[Year ]),"12/31"))
Departure_Cnt_2022 = CALCULATE(SUM(Table1[Aircraft departures (a) ]),DATESYTD(ENDOFYEAR(dateadd(Table1[Year ],-1,Year)),"12/31"))
Departure_Cnt_2023 = CALCULATE(SUM(Table1[Aircraft departures (a) ]),DATESYTD(ENDOFYEAR(Table1[Year ]),"12/31"))

Can you guys help me ?

@amitchandak @parry2k @Ritaf1983 

[parry2k]

@Shiji_Menon solution is attached, tweak it as you see fit.

[Greg_Deckler]

@Shiji_Menon I would suggest creating measures for each of your KPI's. Then use a matrix visual and the "Switch values to rows" toggle under Values | Values | Options in the format pane for the visual.

[Shiji_Menon]

I made an attempt to create the measure. But my lack of knowledge held me back. Could you show me a sample,if its okay? I would really appreciate it.

[Greg_Deckler]

@Shiji_Menon Could you post data data from your original table as text?

[Shiji_Menon]

Year  Month  Month No.Year-MonthHours flown Aircraft km flown ('000)Aircraft departures (a) Total rev pax (TOB) Freight tonnes (TOB) (b) Mail tonnes (TOB) (b) Total RPK ('000) Weight load factor % (b) Total  charter pax (TOB) (d) Charter aircraft departures (d) Aircraft movements Total Flights Inbound to AustraliaFlights Outbound from AustraliaFlights  TotalPassengers  Inbound to AustraliaPassengers  Outbound from AustraliaPassengers   TotalSeat utilisation %  Total44,82,8013,12,82439,87,6003,07,60325,38,7462,39,04424,21,8852,60,052

2023	Jan	1	Jan 2023	85,407	47,191	48,918	8,540	4,092	54,51,349	67.3	5,607	14,630	7,118	7,097	14,215	16,44,117	14,10,567	30,54,684	86.7
2023	Feb	2	Feb 2023	77,209	41,453	45,948	8,790	4,001	46,11,116	66.8	5,255	12,734	6,194	6,170	12,364	14,04,157	11,19,975	25,24,132	81.6
2022	Jan	1	Jan 2022	58,832	31,811	36,732	12,464	2,614	27,00,539	50.9	4,870	5,933	2,672	2,619	5,291	2,58,626	1,83,966	4,42,592	46.2
2022	Feb	2	Feb 2022	50,124	26,438	32,229	13,741	4,066	24,60,092	60.2	4,996	5,713	2,602	2,533	5,135	2,64,168	1,72,285	4,36,453	49.0

[Ashish_Mathur]

Hi,

The data has not been pasted properly so when i try to take it over to an Excel file, it is all meaningless.  Create a Excel file with 2 tabs - Input and Output.  In the Output file, show the exact result you are expecting.

[Shiji_Menon]

I apologize for the incorrect formatting earlier. Please refer to the table provided below

Input :

Year	Month	Month No.	Year-Month	Hours flown	Aircraft km flown ('000)	Aircraft departures (a)	Total rev pax (TOB)	Freight tonnes (TOB) (b)	Mail tonnes (TOB) (b)	Total RPK ('000)	Weight load factor % (b)	Total  charter pax (TOB) (d)	Charter aircraft departures (d)	Aircraft movements Total	Flights Inbound to Australia	Flights Outbound from Australia	Flights  Total	Passengers  Inbound to Australia	Passengers  Outbound from Australia	Passengers   Total	Seat utilisation %  Total
2023	Jan	2	Jan 2023	85,407	47,191	48,918	44,82,801	8,540	4,092	54,51,349	67.3	3,12,824	5,607	14,630	7,118	7,097	14,215	16,44,117	14,10,567	30,54,684	86.7
2023	Feb	1	Feb 2023	77,209	41,453	45,948	39,87,600	8,790	4,001	46,11,116	66.8	3,07,603	5,255	12,734	6,194	6,170	12,364	14,04,157	11,19,975	25,24,132	81.6
2022	Jan	2	Jan 2022	58,832	31,811	36,732	25,38,746	12,464	2,614	27,00,539	50.9	2,39,044	4,870	5,933	2,672	2,619	5,291	2,58,626	1,83,966	4,42,592	46.2
2022	Feb	3	Feb 2022	50,124	26,438	32,229	24,21,885	13,741	4,066	24,60,092	60.2	2,60,052	4,996	5,713	2,602	2,533	5,135	2,64,168	1,72,285	4,36,453	49.0

Output :

AIRLINE OUTPUT SUMMARY	2023	2022	% change
Passengers   Total	55,78,816	8,79,045	+534.645097805004
Aircraft movements Total	27,364	11,646	+134.964794779323
Mail tonnes (TOB) (b)	8,092	6,680	+21.1451467305795
Freight tonnes (TOB) (b)	17,329	26,204	--33.8696527698699

[Ashish_Mathur]

Hi,

Will the years shown in the output range be selected via a slicer or will these years always be 2022 and 2023?