Pivot Tables - Variance and % Variance fields
 

I have a sales application that contains tables of approx 70 fields with
sales data for each year, one of which is a year field. I'd love to combine
the tables into a single table so that I can represent the data in a Pivot
Table, but need to figure out how to show, create, calculate a variance
field, and a % variance field in a pivot table if I do combine the tables.
In the Pivot table I'd want to show:
2004 Sales 2005 Sales Variance % Variance
10 12 2 20%

Note: The Sales field is the same field, just a different year

Any Suggestions?

Create a pivot table with Year in the column area, and sales in the data
area.
In the Pivot Table Wizard, add another copy of the Sales field to the
data area.
Double-click on the second Sales button
Click the Options button
From the Show Data As dropdown, choose Difference From
As the BaseField, select SalesYear
In the Base item list, select (previous)
Click OK

Add a third copy of the Sales field to the data area.
Double-click on the third Sales button
Click the Options button
From the Show Data As dropdown, choose % Difference From
As the BaseField, select SalesYear
In the Base item list, select (previous)
Click OK, click OK, click Finish


CraigS wrote:
I have a sales application that contains tables of approx 70 fields with
sales data for each year, one of which is a year field. I'd love to combine
the tables into a single table so that I can represent the data in a Pivot
Table, but need to figure out how to show, create, calculate a variance
field, and a % variance field in a pivot table if I do combine the tables.
In the Pivot table I'd want to show:
2004 Sales 2005 Sales Variance % Variance
10 12 2 20%

Note: The Sales field is the same field, just a different year

Any Suggestions?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra, That is just awesome! A followup question if I may. As mentioned I
have aprox 70 fields in this table, some of those are components of total
sales, ie.. residential sales, commercial sales etc.. Is their a way to
dynamically select the sales component field from a list external to the
pivot table and have the variance and % of variance calculate on that field
just as you described below, similar to an INDIRECT function/pointer?

"Debra Dalgleish" wrote:

Create a pivot table with Year in the column area, and sales in the data
area.
In the Pivot Table Wizard, add another copy of the Sales field to the
data area.
Double-click on the second Sales button
Click the Options button
From the Show Data As dropdown, choose Difference From
As the BaseField, select SalesYear
In the Base item list, select (previous)
Click OK

Add a third copy of the Sales field to the data area.
Double-click on the third Sales button
Click the Options button
From the Show Data As dropdown, choose % Difference From
As the BaseField, select SalesYear
In the Base item list, select (previous)
Click OK, click OK, click Finish


CraigS wrote:
I have a sales application that contains tables of approx 70 fields with
sales data for each year, one of which is a year field. I'd love to combine
the tables into a single table so that I can represent the data in a Pivot
Table, but need to figure out how to show, create, calculate a variance
field, and a % variance field in a pivot table if I do combine the tables.
In the Pivot table I'd want to show:
2004 Sales 2005 Sales Variance % Variance
10 12 2 20%

Note: The Sales field is the same field, just a different year

Any Suggestions?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

You could try moving those fields to the page area, and selecting items
there. They act as a filter for the results shown in the pivot table.

CraigS wrote:
Debra, That is just awesome! A followup question if I may. As mentioned I
have aprox 70 fields in this table, some of those are components of total
sales, ie.. residential sales, commercial sales etc.. Is their a way to
dynamically select the sales component field from a list external to the
pivot table and have the variance and % of variance calculate on that field
just as you described below, similar to an INDIRECT function/pointer?

"Debra Dalgleish" wrote:


Create a pivot table with Year in the column area, and sales in the data
area.
In the Pivot Table Wizard, add another copy of the Sales field to the
data area.
Double-click on the second Sales button
Click the Options button
From the Show Data As dropdown, choose Difference From
As the BaseField, select SalesYear
In the Base item list, select (previous)
Click OK

Add a third copy of the Sales field to the data area.
Double-click on the third Sales button
Click the Options button
From the Show Data As dropdown, choose % Difference From
As the BaseField, select SalesYear
In the Base item list, select (previous)
Click OK, click OK, click Finish


CraigS wrote:

I have a sales application that contains tables of approx 70 fields with
sales data for each year, one of which is a year field. I'd love to combine
the tables into a single table so that I can represent the data in a Pivot
Table, but need to figure out how to show, create, calculate a variance
field, and a % variance field in a pivot table if I do combine the tables.
In the Pivot table I'd want to show:
2004 Sales 2005 Sales Variance % Variance
10 12 2 20%

Note: The Sales field is the same field, just a different year

Any Suggestions?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra, Thanks but that doesn't work because of the table set up, those
component fields are numeric not categories. I didn't full explain that
below. However I did come up with a solution: First I added a Picklist field
on the Pivot Table sheet and named the range "FieldPick". Second, I added a
field to the table called "AnalysisField" and in that field I entered the
formula:
=HLOOKUP(FieldPick,table1,+CF2,FALSE)
which returns the value of each row in the column/field chosen by the
PickList.

I then replaced the "Sales" filed in your example with "AnalysisField" and
it's good to go.

Note: the +CF2 references the row offset in the table for each row

Thanks again for all your help! Awesome!

"Debra Dalgleish" wrote:

You could try moving those fields to the page area, and selecting items
there. They act as a filter for the results shown in the pivot table.

CraigS wrote:
Debra, That is just awesome! A followup question if I may. As mentioned I
have aprox 70 fields in this table, some of those are components of total
sales, ie.. residential sales, commercial sales etc.. Is their a way to
dynamically select the sales component field from a list external to the
pivot table and have the variance and % of variance calculate on that field
just as you described below, similar to an INDIRECT function/pointer?

"Debra Dalgleish" wrote:


Create a pivot table with Year in the column area, and sales in the data
area.
In the Pivot Table Wizard, add another copy of the Sales field to the
data area.
Double-click on the second Sales button
Click the Options button
From the Show Data As dropdown, choose Difference From
As the BaseField, select SalesYear
In the Base item list, select (previous)
Click OK

Add a third copy of the Sales field to the data area.
Double-click on the third Sales button
Click the Options button
From the Show Data As dropdown, choose % Difference From
As the BaseField, select SalesYear
In the Base item list, select (previous)
Click OK, click OK, click Finish


CraigS wrote:

I have a sales application that contains tables of approx 70 fields with
sales data for each year, one of which is a year field. I'd love to combine
the tables into a single table so that I can represent the data in a Pivot
Table, but need to figure out how to show, create, calculate a variance
field, and a % variance field in a pivot table if I do combine the tables.
In the Pivot table I'd want to show:
2004 Sales 2005 Sales Variance % Variance
10 12 2 20%

Note: The Sales field is the same field, just a different year

Any Suggestions?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Great! Thanks for letting me know how you solved the problem.

CraigS wrote:
Debra, Thanks but that doesn't work because of the table set up, those
component fields are numeric not categories. I didn't full explain that
below. However I did come up with a solution: First I added a Picklist field
on the Pivot Table sheet and named the range "FieldPick". Second, I added a
field to the table called "AnalysisField" and in that field I entered the
formula:
=HLOOKUP(FieldPick,table1,+CF2,FALSE)
which returns the value of each row in the column/field chosen by the
PickList.

I then replaced the "Sales" filed in your example with "AnalysisField" and
it's good to go.

Note: the +CF2 references the row offset in the table for each row

Thanks again for all your help! Awesome!

"Debra Dalgleish" wrote:


You could try moving those fields to the page area, and selecting items
there. They act as a filter for the results shown in the pivot table.

CraigS wrote:

Debra, That is just awesome! A followup question if I may. As mentioned I
have aprox 70 fields in this table, some of those are components of total
sales, ie.. residential sales, commercial sales etc.. Is their a way to
dynamically select the sales component field from a list external to the
pivot table and have the variance and % of variance calculate on that field
just as you described below, similar to an INDIRECT function/pointer?

"Debra Dalgleish" wrote:



Create a pivot table with Year in the column area, and sales in the data
area.
In the Pivot Table Wizard, add another copy of the Sales field to the
data area.
Double-click on the second Sales button
Click the Options button
From the Show Data As dropdown, choose Difference From
As the BaseField, select SalesYear
In the Base item list, select (previous)
Click OK

Add a third copy of the Sales field to the data area.
Double-click on the third Sales button
Click the Options button
From the Show Data As dropdown, choose % Difference From
As the BaseField, select SalesYear
In the Base item list, select (previous)
Click OK, click OK, click Finish


CraigS wrote:


I have a sales application that contains tables of approx 70 fields with
sales data for each year, one of which is a year field. I'd love to combine
the tables into a single table so that I can represent the data in a Pivot
Table, but need to figure out how to show, create, calculate a variance
field, and a % variance field in a pivot table if I do combine the tables.
In the Pivot table I'd want to show:
2004 Sales 2005 Sales Variance % Variance
10 12 2 20%

Note: The Sales field is the same field, just a different year

Any Suggestions?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html