Forum Discussion
Caluculate actuals and forecast
| 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?
3 Replies
- amitchandak
Super User
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
- 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
