Running Balance in excel pivot table

[shripaldalal]

Hi,

Have rows:
Product, transaction no

have data fields:
qty rec, qty iss, balance (calculated field: qty rec - qty iss)

want balance to be a running total with base field of product

so want something like:

=======================================================
product--------doc no-----------------rec--------------------
iss-----------------------balance
=======================================================

acetic acid
-------------------opnbal/
1-----------------4----------------------0----------------------
(4-0)=4
-------------------sales/
inv-----------------0---------------------3-----------------------
(4-3)=1
acetic acid
total:------------------------4---------------------3--------------------------------1

(now again running total should start from 0 for new product)

ammonium nitrate
-------------------opnbal/
1-----------------22--------------------0-----------------------
(22-0)=22
-------------------sales/
inv-----------------0---------------------16---------------------
(22-16)=6
ammonium nitrate
total---------------22--------------------16--------------------------------6

Instead of that what i get right now is:

acetic acid
-------------------opnbal/
1-----------------4----------------------0----------------------
(4-0)= 4
-------------------sales/
inv-----------------0---------------------3-----------------------
(0-3)= -3
acetic acid
total:------------------------4---------------------3--------------------------------1

(now again running total should start from 0 for new product)

ammonium nitrate
-------------------opnbal/
1-----------------22--------------------0-----------------------
(22-0)= 22
-------------------sales/
inv-----------------0---------------------16---------------------
(0-16)= -16
ammonium nitrate
total---------------22--------------------16-------------------------------
7 [22-16=6]+[4-3=1]

any suggestions if something can be done ?

thanks.

shripal.