• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1337
  • Last Modified:

Calculated column in Pivot Table

I have a Pivot Table, and would like a calculated column based on filter values.  For instance, if the user selects a value, say "Generic", I want to discount the sale price by 10%.  But if the user selects brand, I want a column for sale price that equals sale price x 85%.

This is an example of course...this scenario doesn't have the be used.  

Can you create dynamic fields in a pivot table?

0
tobin46
Asked:
tobin46
  • 2
  • 2
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
A calculated field can be added to a PivotTable based on one or more other fields. Calculated fields can be based on fields form the source data or form other calculated fields.

To create a calculated field on Excel 2000 and earlier, right-click in the data area of the pivot table and choose the menu command Formulas->Calculated Field.

On 2002 and 2003, right-click in the data area of the pivot table and choose Show PivotTable Toolbar and then choose the menu command PivotTable->Formulas->Calculated Field.

On 2007 and later, on the Options tab, Tools group, click on Formulas and choose "Calculated Fields".

In the Name text entry field, type a name for the calculated field. In the Formula text entry field, type the formula for the field. To use the data from a field in the formula, click the field in the Fields box, and then click Insert Field. Click Add, and then click OK. See Excel help for more information about how to constuct pivot table formulas and how to reference source data - enter "pivot table formulas" to see the relavent topics.

Notes:

Calculated fields can only be used on non-OLAP source data.

Calculated fields are calculated at the aggregate level, not the item or record level. This means that some formulas may not work as desired such as any formulas with conditional logic.

Kevin
0
 
Jerry PaladinoCommented:
Hi tobin46,
I don't believe you can construct a calculated field that is "dynamic" as you indicated.  You can accomplish what you are describing by adding a field to your datasource.
In the attached example is a lookup table with the associated discount for item.  The total sales price is multiplied by the discount that is read from the lookup table.  Changes to the lookup table change the entire datasource data table so you can make discount adjustment to the entire file with an update of the lookup table.
With this, the pivot table displays the the discounted amount as the page item is changed.  See attached example file.
HTH,
Jerry

PivotTable-Example.xls
0
 
tobin46Author Commented:
ProdOps, that really helped me with some breakthrough thinking.

Follow up.....If I have two calc'd field in the pivot table which are sum fields.  Can I create another filed based on those?

The problem is my data is aggregated.  I ran a query that grouped by month and I'm looking for an actual average sale price.  when I do the "average" function based on sales price, its gives me the average based on that one record which is essentially the aggregated sale price divided by one.
0
 
Jerry PaladinoCommented:
Yes - you should be able to use two calculated fields to produce a third field.  Be careful and cross check subtotal rows and total rows and make sure that the formula you produce delivers the results you expect.  Sometimes you can get results that are accurate based on the formula but not exactly what you are trying to accomplish.  A good manual check is always desirable.
Aggregated Data - Based on your comment - I am assuming you aggregated your data in an Access or SQL query before you brought it into Excel.  If you want the true average selling price then I believe you will need to use the raw data (unaggregated) in Excel to calculate the true average selling price.  Very easy to do in a Pivot Table but you will need the detail data.  Otherwise, you run into the problem you currently face or you are taking an average of an average which can give you skewed results.
HTH,
Jerry
0
 
zorvek (Kevin Jones)ConsultantCommented:
Rather than use the PivotTable to calculate aggregate values, you might be better off doing the calculations in the main or an intermediate table and then running those results into the PivotTable. You might also consider NOT doing any aggregation at the server layer and doing it in Excel instead.

Calculated fields in a Pivot Table are evaluated at the aggregate level, not the record or item level. This means that some calculation formulas such as those that rely on conditional logic will probably not provide the desired results. The workaround is to add additional columns to the source data table that ensures the field is calculated at the record level.

For example, to add a field to a Pivot Table that displays a sum of records meeting a certain criteria, adding a Pivot Table calculated field such as:

   =IF(SalesStatus="Active",Revenue,0)

will be evaluated as:

   =IF(SUM(SalesStatus)="Active",SUM(Revenue),0)

versus:

   =SUM(IF(SalesStatus="Active",Revenue,0))

Kevin
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now