Calculated Variance in Pivot Table

[AntBlabby8]

I am not sure if this can be done--or how to do it.

I have a huge download into excel, more than 10,000 rows of data each month. And I have absolutely no control of how the data comes into Excel.

The data created from and then divided into two sources--original projection (Budget), and the Actual sales. However, the data for each comes in on a separate rows, which is what creates the problem I am having. (and again, I have absolutely no control of how I get the data.)

There are about a dozen variables, but of there are four variables that matter: Customer Name, Customer Number, Item Name, Item Number. Each one has an Amount, and a column called Source of Data which includes only Budget or Actual.

What I need to do is create a table that shows the aggregated Budget v. the Actual, if the four attribute variables (Customer Name, Customer Number, Item Name and Item Number) are the same. That is, I am trying to combine all sales for each customer/item for the period, first as the budget and then the Actual.

Then--and here's where I can't figure out how to do it, I want to create a VARIANCE column.

I inserted two columns in the data's worksheet one called BUDGET, another called ACTUAL. Then I did an IF function in both columns, referencing the Source of Data Column, so that the amount gets put into the new column as appropriate (e.g., in the Budget the IF pulls in the number from the Amount column, if the Source for that row is Budget, otherwise it enters Zero).

That makes a great Pivot Table that has as columns Budget and Actual to compare. But what I can't figure out how to get is a Pivot Table that will show the Variance: which is what I am aiming for. Because there are multiple entries, I can't do a VLookup or I'll just get one piece of the data.

Can anyone think of how to do this, especially inside a Pivot?

THANKS

 

[Fazza]

Hi, Joan.

Here is one appraoch that doesn't change the source data. So not even adding your current two extra columns.

Basically, it involves doing the manipulation work in SQL in between the source data and the pivot table.

I have assumed the data worksheet is named Sheet1 and the salient headers are exactly - without qoutes, though not case sensitive, and in any order - "Source of Data", "Customer Name", "Customer Number", "Item Number", "Amount". Headers in row 1 and data under.

The SQL is

SELECT `Source of Data`, `Customer Name`, `Customer Number`, `Item Number`, Amount
FROM [Sheet1$]
UNION ALL
SELECT 'variance' AS [Source of Data], `Customer Name`, `Customer Number`, `Item Number`, Iif(`Source of Data`='budget',-1,1)*Amount AS [Amount]
FROM [Sheet1$]

To apply, save the data file. Go to a new file (to avoid memory leak problems when creating. Once created, the final pivot table can be moved back into the source data file if you like) and start either at the 'external data source' at the first step of the pivot table wizard, or, the way I usually do, via menu data, import external data, new database query. Select Excel files, OK, select your Excel file. Hit OK to any message about no visible ranges and then take the 'options' button and then select 'system tables' and OK: this will now show source file worksheets name. [BTW, defined names can be used instead. These are picked up by default without needing to set 'system tables' (worksheets) so long as the defined names are NOT dynamic.] Select your worksheet, as above I have assumed it is named Sheet1, and pick the required fields so they appear on the RHS. Continue a few steps and when you have the option take the choice to edit in MS Query. In this interface hit the 'SQL' button and edit the text so that what you initially see is replaced by the SQL above. The SQL is just text, so it is simple to change. Hit 'OK' to enter the SQL, and OK to any message about not being able to graphically represent whatever. It is only a simple warning and all is OK. Now hit the 'open door' button to exit the MS Query interface and then take the pivot table option.

Note, I have assumed that variance is actual - budget. If you want budget-actual, just swap the 1 and -1 in the "Iif(comparison,-1,1) ..."

As above, if you like, you can move this pivot table back into the same file as the data, or leave it separate.

Regards, Fazza

 

[AntBlabby8]

Thanks. I'll try it the next chance I get. Such a detailed explanation. And it makes sense to me, which is great. It is so much appreciated!