Forum

Pivot Table from po...
 
Notifications
Clear all

Pivot Table from power query table - Opening and Closing Stock

3 Posts
2 Users
0 Reactions
150 Views
(@dianechisholm)
Posts: 2
New Member
Topic starter
 

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.Frown

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
 
Posted : 08/08/2021 5:38 am
Anders Sehlstedt
(@sehlsan)
Posts: 971
Prominent Member
 

Hello Diane,

If you can provide a sample file with this data that would be great.

Br,
Anders

 
Posted : 08/08/2021 4:21 pm
(@dianechisholm)
Posts: 2
New Member
Topic starter
 

Hi Anders,

The file is private, cannot not send.

Diane

 
Posted : 09/08/2021 5:58 pm
Share: