Add a calculated field to a pivot table

  • I'm a little confused as to How you want this to happen but to insert add a column to a piviot table, you'll need to include this in your code:


    Hopefully this will give you a start and maybe someone that is a little more familiar with piviot tables can help you refine it to your needs.

  • If you aren't using VBA to do this, then I think you may be in the wrong forum. If what you are looking for is to have just a "Third Column" with the percentage result, that would be a question for the "Formulas" forum. the column would just be exactly that, another column. then the percentage formula would be added to the cell for calculation.


    I would at least try to post your question there and see anyone can help you with it.


    Have a good day! sorry i couldn't be more help!

  • Are Fail and Pass separate columns in your source data, or items in the same column?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • OK, and are you trying to see Pass/Fail, or the percentage each one is of the total for the 8 items?


    It would really help if you could post a workbook.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • You need a calculated item defined as:


    =Pass /(Fail +Pass )


    but you will have to format those cells as percentage separately.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • You can add an additional check like:


    =IF(fail+pass=0,0,Pass /(Fail +Pass ))


    or just modify the pivot table options to hide errors.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Using the CalculatedFields.Add Method to create a calculated field. Refer Image 1a which shows a PivotTable report named "PivotTable1" showing Sales using the summary function of Sum. Image 1b shows the Pivot report with a new calculated field added "Variance-%", using the below code. Note that the source range field names are "Sales" and "Budgeted Sales" on which calculations are based.


  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.


    How to use code tags

    Note: no apostrophe in the tags, just used for demonstration here.

    ['code]


    your code goes between these tags


    ['/code]


    Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.


    Thanks.

  • Step 1: Create the calculated field

    In a worksheet in Tableau, select Analysis > Create Calculated Field.

    In the Calculation Editor that opens, give the calculated field a name.


    Step 2: Enter a formula

    In the Calculation Editor, enter a formula.

    Eg. SUM([Pass])/SUM([Fail])

    Formulas use a combination of functions, fields, and operators.


    Step 3: When finished, click OK.

    The new calculated field is added to the Data pane. If the new field computes quantitative data, it is added to Measures. If it computes qualitative data, it is added to Dimensions.


    Regards,

    Jerry.