Pivot Table Adding Field or Column

shah0101

Board Regular
Joined
Jul 4, 2019
Messages
139
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?

view


SCREENSHOTS ATTACHED.


PLEASE GUIDE / ADVISE.




view



view
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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?

view


SCREENSHOTS ATTACHED.


PLEASE GUIDE / ADVISE.




view



view








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

https://drive.google.com/file/d/1Mb2myToZOn-sT9t6eTkOmBSM24g_pZXO/view?usp=sharing
 
Upvote 0
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" ?
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top