# How can add calculated Variance to Pivot table when Budget & Cost are from different tables in the data model

#### Dave Bench

##### New Member
Please bear with me, I'm a newbie!
I'm using PowerPivot in Excel 2010 and am trying to create a calculated Variance field in a pivot table rather than add a column to my source tables as my Budgets and Costs come from 2 separate sources.

I have 4 tables in my PowerPivot data model:
Budget (fields: AccountCode, Month, Value)
Cost (fields: AccountCode, Month, Value)
Month (fields: Month, FiscalYear, FiscalQuarter)
Account (fields: AccountCode, Description)

The following relationships exist between the 4:
Budget[Month] = Month[Month]
Cost[Month] = Month[Month]
Budget[AccountCode] = Account[AccountCode]
Cost[AccountCode] = Account[AccountCode]

I'm then trying to create a pivot table giving Budget, Cost and Variance (Budget-cost) but the option to create a calculated field is not available.

Is there a way to do this when the Budget & Costs sit in different source tables?

Any help greatly appreciated

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### DWig

##### Board Regular
Dave,

I have a similar tool which I've built. You're going to want to build a Measure. The basic structure is SUM(Budget[Value])-sum(Cost[Value])

Open the PowerPivot window and click "PivotTable". This will launch a PowerPivot pivot table in the normal Excel interface. Put the Account[AccountCode] field in the Row Label, the Sum of Budget[Value] and Cost[Value] as values in the PivotTable. Right-click the Cost table in the PowerPivot Field List, and click "Add New Measure". Type in the above function I put for the basic structure, and you should be good. Let me know if this doesn't get you where you need to be.

DWig

#### Dave Bench

##### New Member
Many thanks DWig - that does the trick!

#### Ned21

##### New Member
Good afternoon!

Im using excel 2010 and I need to create a pivot table that is able to tell the montly growth and also the growth between january of 2012 and 2013... february of 2012 and 2013 and so on. Beside that it should be able to tell the quarterly growth and quarterly growth between the first quarter of 2012 and the first quarter of 2013.... and so on with the quarters. Other than that it should be able to tell the annual growth .

I only have one source table with 11 different columns for the different products and 3 years worth of sales data , so it s a fairly small database.

Is it possible to visualise all this on one pivot table? , because my boss wants to have it on one or two pivot tables to see it easier.

Thank you for the help !

### Similar threads

Replies
0
Views
953
Replies
0
Views
178
Replies
1
Views
269
Replies
3
Views
290
Replies
1
Views
367

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Threads
1,152,255
Messages
5,769,067
Members
425,515
Latest member
baltusf

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back