+ Reply to Thread
Results 1 to 8 of 8

Pivot Table Adding Field or Column

  1. #1
    Registered User
    Join Date
    05-31-2008
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    7

    Pivot Table Adding Field or Column

    Hi experts,

    i have pivot table which is gathering data from two different tables. Relationship is working fine (i think) but i am stuck at adding a "field" or a "column" to get the results by which will be the end product of deduction from "table 1" to the items of "table 2". Both values are already bringing numbers to the "pivot table".

    1) calculated field / calculated item is grayed for some reason.

    2) to explain what i am trying to achieve is as below:

    A) pivot table is a combination of two different tables. Table one is named "lc" and table two is "inv".
    B) "lc" table has a column/field named "amount" and "inv" table has a column/filed named "value".
    C) both "amount" and "value" fields are in pivot table which are bringing data as i need.
    D) "amount" filed is displayed once in each group and the "value" field is repeating several rows under same group which is fine.
    E) now i want a separate column or field where i can deduct the subtotal "value" from the "amount" field. Please guide / advise how to create / add one?




    Please guide / advise.
    Attached Images Attached Images

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Pivot Table Adding Field or Column

    Administrative note
    Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
    Thanks

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Pivot Table Adding Field or Column

    Hi and welcome
    although we value privacy as much as anyone else, it could be important that members have a rough idea of your location. You might in the future post questions which are bound to your regional settings.
    So, please update your profile ( country will suffice, no need to be more precise).
    Thank you for helping us to help you

  4. #4
    Registered User
    Join Date
    05-31-2008
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Pivot Table Adding Field or Column

    Using office 365 - updated on profile as well.

  5. #5
    Registered User
    Join Date
    05-31-2008
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Pivot Table Adding Field or Column

    I am posting these from Hong Kong - updated profile as well.

  6. #6
    Registered User
    Join Date
    05-31-2008
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Pivot Table Adding Field or Column

    Attachment 644823

    if above is not workable or for some reasons we can't incorporate that then is it possible that we can add a "measure" to the pivot table that can bring up the result that we need?

    Attached is the screenshot.


    Please guide / advise.

  7. #7
    Registered User
    Join Date
    05-31-2008
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Pivot Table Adding Field or Column

    Okay, if above is too complicated / messy can anyone please help me write how to put value in to a calculated column?

    Picture is attached.

    Thanks in advance.



    Attachment 645205

  8. #8
    Registered User
    Join Date
    05-31-2008
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Pivot Table Adding Field or Column

    WELL I FINALLY FIGURED IT OUT HOPE IT HELPS OUT OTHERS:


    TASK:
    I have two tables: one of customers ("Donor"), and one of transactions ("Trans"). In Donor, I want a "Total" column that sums all the transactions by a particular Donor ID, which I would calculate in a standard Excel table. The key here is to make sure that the relationship between the two tables is correctly configured, and then make sure to use the combination of CALCULATE() and FILTER() -- filtering the trans table based on the current donor context.

    FOLLOWING IS WHAT ONE NEEDS TO WRITE:
    =CALCULATE(SUM(Trans[Amt]), FILTER(Trans, Trans[Donor] = Donor[DonorKey]))



    HOPE THIS HELPS!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 01-15-2019, 08:54 PM
  2. [SOLVED] Adding additional field to pivot table (but not for each column)
    By cprobertson1 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-21-2017, 07:51 AM
  3. [SOLVED] Referring to a Sub-Field on Calculated Field Pivot Table Column?
    By figo12 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-27-2014, 02:02 PM
  4. Replies: 3
    Last Post: 03-03-2012, 12:16 PM
  5. Adding new calculated/column field to pivot table
    By MARYAN in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-09-2010, 05:34 AM
  6. Replies: 3
    Last Post: 02-23-2009, 10:07 AM
  7. Pivot Table - adding a new field
    By Pivot Table in forum Excel General
    Replies: 2
    Last Post: 06-09-2005, 03:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1