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
<colgroup><col><col></colgroup><tbody>
</tbody>
So then I roll this into a pivot table:
<colgroup><col><col><col></colgroup><tbody>
</tbody>
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?
<colgroup><col><col><col></colgroup><tbody>
</tbody>
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
| FULL ACCOUNT NAME | ACCOUNT NUMBER |
100000 External sales roduct | 100000 RPT |
| 100070 Transport Brokerage Revenue | 100070 RPT |
| 110000 Internal sales product:w/in legal entity | 110000 RPT |
<colgroup><col><col></colgroup><tbody>
</tbody>
So then I roll this into a pivot table:
| * External Product Quantity | 100000 External salesroduct | 7,000 |
| 100070 Transport Brokerage Revenue | 2,000 | |
| * External Product Quantity Total | 9,000 | |
| * Internal Product Quantity | 110000 Internal sales product:w/in legal entity | 11,000 |
| * Internal Product Quantity Total | 20,000 |
<colgroup><col><col><col></colgroup><tbody>
</tbody>
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?
| * Product Revenue | 100000 External salesroduct | 7,000 |
| 100070 Transport Brokerage Revenue | 2,000 | |
| 110000 Internal sales product:w/in legal entity | 11,000 | |
| 132000 Proceeds on sale: operating noncurr | 0 | |
| 132099 Proceeds on sale: operating noncurr | 0 | |
| 142000 Net gain on sale: operating oth NCA | 0 | |
| 142099 Net gain on sale: operating oth NCA | 0 | |
| 145800 Other non trading revenue, external: | 0 | |
| 152000 Proceeds on sale: operating oth NCA | 0 | |
| 152099 Book value of op non curr assets dis | 0 | |
| 172000 Net loss on sale: operating oth NCA | 0 | |
| 172099 Net loss on sale: operating oth NCA | 0 | |
| 185000 Discount received from vendors | 0 | |
| 185500 Underages and overages | 0 | |
| 195800 Other non trading expenses TD | 0 | |
| 202001 GRIR write-off to P&L | 0 | |
| 202012 Raw material inventory quantity chan | 0 | |
| 202018 Spares,stores & proc matl inventory | 0 | |
| 202020 Fuel Inventory Quantity Change | 0 | |
| 203002 Raw Materials: Variances Price Relat | 0 |
<colgroup><col><col><col></colgroup><tbody>
</tbody>
