Caluculate actuals and forecast

[Anonymous]

fcjun2021	nov	dec	jan	feb	mar	apr	may	jun	jul	aug	sept	oct
a	100	200	300	400	500	600	700	800	900	1000	1500	2000
b	50	60	70	80	90	100	110	120	130	140	150	160
c	10	20	30	40	50	60	70	80	90	100	110	120
volume								333

fcmay2021	nov	dec	jan	feb	mar	apr	may	jun	jul	aug	sept	oct
a	100	200	300	400	500	600	700	600	900	1000	1500	2000
b	50	60	70	80	90	100	110	160	130	140	150	160
c	10	20	30	40	50	60	70	80	90	100	110	120
volume								280

here the month jun actual is 333 (avg of dept a,b,c) and forcast should be 280 that is coming from previous month so when i select month jun and dept a it should be 800 other than a,b,c i need to display  333 as actual ,fc will be change according dept, how can i achieve it?

[amitchandak]

@Anonymous , Is this your raw data structure. If so, you need to unpivot data.

Second, can you explain the calculation of 333  and 280 by example 

[Anonymous]

Raw data is same like this only,333 and 280 are average of that column

[amitchandak]

@Anonymous , In the power query, create one more copy of the table (T1_Copy). Use Group by and take Avg of all required columns

Now Append the tables T1 and T1_copy .(Make sure column name are same for the columns you want to append )

Do same for another table