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

Dave Bench

New Member
Joined
Sep 4, 2013
Messages
3
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
Joined
Sep 26, 2012
Messages
77
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


 

Ned21

New Member
Joined
Sep 24, 2013
Messages
1
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 !
 
Master Excel Bundle

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.

Forum statistics

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

Which adblocker are you using?

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
Top