Depending on your version of Desktop and your data source (this will work in Excel) you can do this ....
- connect to the Excel spreadsheet
- in the data preview pane, select the Actuals columns (Jan thru Dec), then right click and choose "Pivot"
- then select your Forecast columns and choose "Add Data to Pivot"
- you now have 2 columns - Pivot Field Values (which you might want to rename to Values) and Pivot Field Names
- create a calculated field called Month and use this formula : left ([Pivot Field Names],find([Pivot Field Names],' '))
- create a calculated field called Scenario and use this formula : mid ([Pivot Field Names],find([Pivot Field Names],' '))
- you can also hide Pivot Field Names
The data should be in the right format and easier to use in Desktop.
Thanks for the tip! I don't think I explained my question properly.. At the moment if I pivot all my months actuals and forecast, the actual and forecast numbers are all put into one column with the other pivot column being the month. With that I'm not able to differentiate which is actuals and forecast. This is making it tricky for me to do Actuals and Forecast comparisons.
Would you have any solutions?