Pivot Table Calculated Item Includes all accounts from Profit and Loss Statement

Grimm127

Board Regular
Joined
Aug 17, 2011
Messages
132
GM Excel Crew,

I am having trouble and I need some expert advice. I am recreating a PnL statement using a Pivot table. It gives me lots of flexibility for my needs for analysis. However, there is one problem that is driving me nuts. In this PnL there are lets say 50 accounts. Of course its broken out in Expenses, Rev etc. However here is my issue. In the data table, I have 2 columns, one is the account name and number and a second column for its bucket. Like the example below

[TABLE="width: 532"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]FULL ACCOUNT NAME[/TD]
[TD]ACCOUNT NUMBER[/TD]
[/TR]
[TR]
[TD] 100000 External sales:product[/TD]
[TD]100000 RPT[/TD]
[/TR]
[TR]
[TD] 100070 Transport Brokerage Revenue[/TD]
[TD]100070 RPT[/TD]
[/TR]
[TR]
[TD] 110000 Internal sales product:w/in legal entity[/TD]
[TD]110000 RPT[/TD]
[/TR]
</tbody>[/TABLE]

So then I roll this into a pivot table:

[TABLE="width: 777"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]* External Product Quantity[/TD]
[TD] 100000 External sales:product[/TD]
[TD="align: right"]7,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 100070 Transport Brokerage Revenue[/TD]
[TD="align: right"]2,000[/TD]
[/TR]
[TR]
[TD]* External Product Quantity Total[/TD]
[TD] [/TD]
[TD="align: right"]9,000[/TD]
[/TR]
[TR]
[TD]* Internal Product Quantity[/TD]
[TD] 110000 Internal sales product:w/in legal entity[/TD]
[TD="align: right"]11,000[/TD]
[/TR]
[TR]
[TD]* Internal Product Quantity Total[/TD]
[TD] [/TD]
[TD="align: right"]20,000[/TD]
[/TR]
</tbody>[/TABLE]

Looks Great but I need to add one more line which I am using the Calculated Field via item to add the two Totals above together (Named * Product Quantity) . Here's the rub. When I do that my results look like this below. In a nut shell, it is including all the other accounts. I don't understand why its doing this. I only want to show what that I specifically put to the formula. What am I missing?

[TABLE="width: 777"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]* Product Revenue[/TD]
[TD] 100000 External sales:product[/TD]
[TD="align: right"]7,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 100070 Transport Brokerage Revenue[/TD]
[TD="align: right"]2,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 110000 Internal sales product:w/in legal entity[/TD]
[TD="align: right"]11,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 132000 Proceeds on sale: operating noncurr[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 132099 Proceeds on sale: operating noncurr[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 142000 Net gain on sale: operating oth NCA[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 142099 Net gain on sale: operating oth NCA[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 145800 Other non trading revenue, external:[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 152000 Proceeds on sale: operating oth NCA[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 152099 Book value of op non curr assets dis[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 172000 Net loss on sale: operating oth NCA[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 172099 Net loss on sale: operating oth NCA[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 185000 Discount received from vendors[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 185500 Underages and overages[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 195800 Other non trading expenses TD[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 202001 GRIR write-off to P&L[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 202012 Raw material inventory quantity chan[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 202018 Spares,stores & proc matl inventory[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 202020 Fuel Inventory Quantity Change[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 203002 Raw Materials: Variances Price Relat[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Forum statistics

Threads
1,223,164
Messages
6,170,444
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