Calculated column in Pivot Table

Posted on 2009-04-29
Last Modified: 2012-05-06
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?

Question by:tobin46
    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    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.


    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.

    LVL 16

    Expert Comment

    by:Jerry Paladino
    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.

    LVL 1

    Author Comment

    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.
    LVL 16

    Expert Comment

    by:Jerry Paladino
    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.
    LVL 81

    Accepted Solution

    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:


    will be evaluated as:





    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now