I'm currently working in an OLAP Cube for a company.
I have 3 main fields that the customer wants to be able to pull on excel.
- Actual
- Forecast
- Budget
And along with it he wants a comparison between them, and the % variation. For instance (Actual - Forecast and (Actual/Forecast)-1)
As I am required to use an aggregated function to show the values in excel, doing the first one was simple, i just had to sum those values in each row and the aggregated sum would give me what i want.
But now I am having trouble thinking on how I can use only the sum of actual and sum of forecast and create the % variation fields.
I thought about creating a cumulative sum of each field and use the aggregate value that uses only the last row value, but I don't know how to do it or if there's another way.
PS: I know this would be a simple thing to do in excel, but the customer wants it to be available as he create his pivot table.
