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

[Dave Bench]

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

 

[DWig]

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]

Many thanks DWig - that does the trick!

 

[Ned21]

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 !