Pivot Table Adding Field or Column

shah0101

New Member
Joined
Jul 4, 2019
Messages
38
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?



SCREENSHOTS ATTACHED.


PLEASE GUIDE / ADVISE.







 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

shah0101

New Member
Joined
Jul 4, 2019
Messages
38
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?



SCREENSHOTS ATTACHED.


PLEASE GUIDE / ADVISE.














https://drive.google.com/file/d/1el52YYwZZHr3274RxpBjnxs6w59Ht61K/view?usp=sharing

https://drive.google.com/file/d/1Mb2myToZOn-sT9t6eTkOmBSM24g_pZXO/view?usp=sharing
 

shah0101

New Member
Joined
Jul 4, 2019
Messages
38
If that is not workable is it possible to "add measure" to solve the problem?

Like can we deduct the "subtotal of the group": "lc number" from "amount" ?
 

shah0101

New Member
Joined
Jul 4, 2019
Messages
38
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!
 

Forum statistics

Threads
1,089,485
Messages
5,408,536
Members
403,213
Latest member
Rvbicon

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top