Subtracting the difference between 2 values in a pivot while still showing the original values

[Tan9321]

HI, Can any one help me on this

The months shown below are values , with the heading on the column being MONTH, How can I subtract the two months while still showing the values of the months
I cant put in a calculated field as Month is the only heading, also if I use the Show total as difference between the 2 columns it will clear July 15 while only putting the difference of the two columns in the July 16 column and nothing in Sub Total

I have asked a couple of people who are advanced in excel but we are all at a loss on this, and yet it seems like it should be quite obvious
Data in which the table is shown is only dummy data as an example , actual file I would use would have a lot more columns which If I was doing something like this I would throw it all into a pivot to show July 16 and July 15 as headers and then pivot that data as my new table

Thanks a mill

T

Sum of VALUE	Column Labels			Sum of VALUE	Column Labels
Row Labels	Jul-15	Jul-16	Grand Total		Row Labels	Jul-15	Jul-16	Difference
HOME	4500	9100	13600		HOME	4500	9100	4600
LADIES	2900	7500	10400		LADIES	2900	7500	4600
MENS	1225	5250	6475		MENS	1225	5250	4025
Grand Total	8625	21850	30475		Grand Total	8625	21850	13225

<colgroup><col><col span="3"><col span="3"><col><col></colgroup><tbody>
</tbody>

GROUP	MONTH	VALUE
MENS	Jul-15	100
MENS	Jul-15	125
MENS	Jul-15	150
MENS	Jul-15	175
MENS	Jul-15	200
MENS	Jul-15	225
MENS	Jul-15	250
LADIES	Jul-15	275
LADIES	Jul-15	300
LADIES	Jul-15	325
LADIES	Jul-15	350
LADIES	Jul-15	375
LADIES	Jul-15	400
LADIES	Jul-15	425
LADIES	Jul-15	450
HOME	Jul-15	475
HOME	Jul-15	500
HOME	Jul-15	525
HOME	Jul-15	550
HOME	Jul-15	575
HOME	Jul-15	600
HOME	Jul-15	625
HOME	Jul-15	650
MENS	Jul-16	675
MENS	Jul-16	700
MENS	Jul-16	725
MENS	Jul-16	750
MENS	Jul-16	775
MENS	Jul-16	800
MENS	Jul-16	825
LADIES	Jul-16	850
LADIES	Jul-16	875
LADIES	Jul-16	900
LADIES	Jul-16	925
LADIES	Jul-16	950
LADIES	Jul-16	975
LADIES	Jul-16	1000
LADIES	Jul-16	1025
HOME	Jul-16	1050
HOME	Jul-16	1075
HOME	Jul-16	1100
HOME	Jul-16	1125
HOME	Jul-16	1150
HOME	Jul-16	1175
HOME	Jul-16	1200
HOME	Jul-16	1225

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[oldbrewer]

GROUP

MONTH

VALUE

MENS

Jul-15

100

MENS

Jul-15

125

MENS

Jul-15

150

MENS

Jul-15

175

MENS

Jul-15

200

MENS

Jul-15

225

MENS

Jul-15

250

Jul-15

Jul-16

TOTAL

INCREASE

LADIES

Jul-15

275

MENS

1225

5250

6475

4025

LADIES

Jul-15

300

LADIES

2900

7500

10400

4600

LADIES

Jul-15

325

HOME

4500

9100

13600

4600

LADIES

Jul-15

350

LADIES

Jul-15

375

LADIES

Jul-15

400

LADIES

Jul-15

425

LADIES

Jul-15

450

HOME

Jul-15

475

a very simple sumproduct table that automatically updates as you add data in cols A:C

HOME

Jul-15

500

seems to do what you want

HOME

Jul-15

525

HOME

Jul-15

550

HOME

Jul-15

575

cell giving 1225

HOME

Jul-15

600

=SUMPRODUCT(($A$2:$A$47=$H9)*($B$2:$B$47=I$8)*($C$2:$C$47))

HOME

Jul-15

625

HOME

Jul-15

650

MENS

Jul-16

675

MENS

Jul-16

700

<colgroup><col width="64" span="17" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Tan9321]

HI, Its for a pivot though that will be using new data all the time, I would have to do this on the side outside the pivot wouldnt I?

 

[SpillerBD]

You want to change the calculation for a Value Field Settings.
Still need a "calculated field, though it will just be to duplicate your Value field.
Then use "Value Field Settings" under the "Show Value As" to "Difference From" and set Base field to Month and Base item to "(previous)"

 

[oldbrewer]

add new data to mine and the table will update automatically, but it is not a pivot table