
Diane Chisholm
New Member
Members
Power Query
Power Pivot
Power BI
Power Query
Power Pivot
Power BI

Forum Posts: 2
Member Since:
June 29, 2017
June 29, 2017

Hi,
I have a trading statement in pivot table.
Issue, the grand total sums the row, only want opening balance at beginning of the year to display in grand total/ total column, and same with closing stock.
I have provided an example of data below and display of pivot table.
Your assistance is appreciated.
Pivot table has the following headings with row items:
Revenue items
Cost of Goods Sold items
Operational Expense items
Grand Total
In the Cost of Goods Sold items, i have a row for opening stock and a row for closing stock.
Monthly pivot
2021 Total | |||||||||||||
January | February | March | April | May | June | July | August | September | October | November | December | ||
Opening Stock | 3521 | 4000 | 3000 | 2000 | 3000 | 4000 | 0 | 0 | 0 | 0 | 0 | 0 | 3521 |
Closing Stock | -4000 | -3000 | -2000 | -3000 | -4000 | -3000 | 0 | 0 | 0 | 0 | 0 | 0 | -3000 |
Yearly pivot
2020 | 2021 | |
Opening Stock | 2950 | 3521 |
Closing Stock | -3521 | -3000 |
Data entered in worksheet:
Account Name | Transaction Date | Amount |
Opening Stock | 1/01/2021 | 3521 |
Closing Stock | 31/01/2021 | -4000 |
Opening Stock | 1/02/2021 | 4000 |
Closing Stock | 28/02/2021 | -3000 |
Opening Stock | 1/03/2021 | 3000 |
Closing Stock | 31/03/2021 | -2000 |
Opening Stock | 1/04/2021 | 2000 |
Closing Stock | 30/04/2021 | -3000 |
Opening Stock | 1/05/2021 | 3000 |
Closing Stock | 31/05/2021 | -4000 |
Opening Stock | 1/06/2021 | 4000 |
Closing Stock | 30/06/2021 | -3000 |
Opening Stock | 1/07/2021 | 3000 |
Closing Stock | 31/07/2021 | 0 |
Opening Stock | 1/08/2021 | 0 |
Closing Stock | 31/08/2021 | 0 |
Opening Stock | 1/09/2021 | 0 |
Closing Stock | 30/09/2021 | 0 |
Opening Stock | 1/10/2021 | 0 |
Closing Stock | 31/10/2021 | 0 |
Opening Stock | 1/11/2021 | 0 |
Closing Stock | 30/11/2021 | 0 |
Opening Stock | 1/12/2021 | 0 |
Closing Stock | 31/12/2021 | 0 |
Opening Stock | 1/01/2022 | 0 |
Closing Stock | 31/01/2022 | 0 |
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Nicholas Meyer, Vincent Malone II, Lakisha Hall
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 880
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 237
Jessica Stewart: 219
A.Maurizio: 213
Aye Mu: 201
jaryszek: 183
Newest Members:
Herbie Key
Trevor Pindling
Stevan Kanjo
Erin Sheldon
Nikita Bhatia
Sheilah Taylor
Clare Webber
David Jenssen
Dominic Brosnahan
Young You
Forum Stats:
Groups: 3
Forums: 24
Topics: 6526
Posts: 28576
Member Stats:
Guest Posters: 49
Members: 32809
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: Velouria, Riny van Eekelen
© Simple:Press —
