Budget versus Actual, subtraction within pivot table

[mellerbeck]

I have sales data for each period, as well as budget data like this:

Period     Year               Sales

1           2011                10

1           2011-Budget     9

2           2011                10

2           2011-Budget     9

I've been able to create the pivot table but I can't figure out how to have a row that shows the difference between Budget versus actual i.e.

              Period          1               2

2011                         10             10

2011-Budget               9               9

Over/(Under)               1               1

Any help would be much appreciated!

Thanks

[lironbaram · Accepted solution]

hei check this example

[mellerbeck]

That was exactly what I needed, Thank you! You made it look too easy!

From what I understood, you used the index to check for budget in the name of the year, and if it was there you flipped it to a negative.

I don't completely understand the first if

if(isnull)only(year))

If you have a chance would you mind explaining that portion?

[lironbaram]

hei

ok the first if checks the dimension and it will be null only on the total row

so the first if is used to find total rows and put there a diffrent formula for the expression