Formula in pivot table

[sharshra]

I want to insert a formula in the pivot table. I tried to include a 'calculated field', but couldn't get the correct result when I have to calculate based on 2nd level. I think the example makes it clear.

It is an example from stock trading book. I have to calculate what quantity of stock is left after buy / sell actions. Also, I have to calculate if there is profit or loss on overall stock. I have added quantity & value for each stock for buy & sell actions in a pivot table. What I want to achieve is shown in separate columns (final qty, investment / gain) outside the pivot table. These columns show the difference between quantity & price for sell & buy. I want these columns to be part of the pivot table.

When I include calculated field in pivot table, it just subtracts without considering buy / sell. It doesn't give the required result. I'm sure there must be a way to do this & I'm making some silly mistake Can someone help me to fix this please?

Base data -

pivot.xlsx
	B	C	D	E	F	G	H
2	Sl.#	Date	Stock name	Action	Qty	Price	Value
3	1	19-Jan-22	xxx	Buy	500	61.75	30,875.00
4	2	24-Jan-22	yyy	Buy	200	97.00	19,400.00
5	3	25-Jan-22	xxx	Sell	500	65.00	32,500.00
6	4	27-Jan-22	yyy	Sell	200	96.50	19,300.00
7	5	27-Jan-22	zzz	Buy	100	77.00	7,700.00
trade book

Cell Formulas
Range	Formula
H3:H7	H3	=F3*G3

Cells with Data Validation
Cell	Allow	Criteria
E3:E7	List	Buy,Sell

Pivot table -

pivot.xlsx
	K	L	M	N	O	P	Q
4		Column Labels
5		Buy		Sell
6	Row Labels	Sum of Qty	Sum of Value	Sum of Qty	Sum of Value	Final qty	Investment / gain
7	xxx	500	30875	500	32500	0	1625
8	yyy	200	19400	200	19300	0	-100
9	zzz	100	7700			100	-7700
10	Grand Total	800	57975	700	51800		-6175
trade book

Cell Formulas
Range	Formula
P7:P9	P7	=L7-N7
Q7:Q9	Q7	=O7-M7
Q10	Q10	=SUM(Q7:Q9)

 

[Alex Blakenburg]

You are trying to do Calculated Field, you want Calculated Item.
Click on either the word Buy or Sell in the pivot heading and then under PivotTable Analyze pick calculated item.
Then calculate the difference between buy and sell.

(Buy & Sell are in the same field in the underlying data which makes them Items in a field. If they are in different columns in the underlying data then they are separate fields so you use calculated fields)

 

[sharshra]

Thanks, Alex. Your solution fixed my problem As always, you are of great help. Really appreciate?

I would like to make few changes though. Now I have the formula 'Diff=Sell-Buy'. It is correct for the value field. For quantity field, I would like to have 'Diff=Buy-Sell'. Is it possible to have 2 different formulas?

Also, I have another problem. When I try to group fields in another pivot table, it doesn't allow me to group. I believe it is due to linking of different pivot tables referring same base data. I get the following message. It is one of very annoying thing in excel? I don't know why pivot tables should be linked by default & why is it complicated to unlink them? I'm trying to search a solution to fix this. Any tips for the same will help.

Pivot table -

pivot.xlsx
	K	L	M	N	O	P	Q
4		Column Labels
5		Buy		Sell		Diff
6	Row Labels	Sum of Qty	Sum of Value	Sum of Qty	Sum of Value	Sum of Qty	Sum of Value
7	xxx	500	30875	500	32500	0	1625
8	yyy	200	19400	200	19300	0	-100
9	zzz	100	7700			-100	-7700
10	Grand Total	800	57975	700	51800	-100	-6175
trade book

Base data -

pivot.xlsx
	B	C	D	E	F	G	H
2	Sl.#	Date	Stock name	Action	Qty	Price	Value
3	1	19-Jan-22	xxx	Buy	500	61.75	30,875.00
4	2	24-Jan-22	yyy	Buy	200	97.00	19,400.00
5	3	25-Jan-22	xxx	Sell	500	65.00	32,500.00
6	4	27-Jan-22	yyy	Sell	200	96.50	19,300.00
7	5	27-Jan-22	zzz	Buy	100	77.00	7,700.00
trade book

Cell Formulas
Range	Formula
H3:H7	H3	=F3*G3

Cells with Data Validation
Cell	Allow	Criteria
E3:E7	List	Buy,Sell

 

[Alex Blakenburg]

I would like to make few changes though. Now I have the formula 'Diff=Sell-Buy'. It is correct for the value field. For quantity field, I would like to have 'Diff=Buy-Sell'. Is it possible to have 2 different formulas?

Yes & No. You can create a new calculation (just give it a different name). However both Qty and Value will show both names.
Don't you think it is going to be a bit confusing to have the positive and negative reversed on the 2 value columns ?

why is it complicated to unlink them? I'm trying to search a solution to fix this. Any tips for the same will help.

Yes unlinking 2 pivots from the same Cache is a bit fiddly. If you are ok with running some code that is probably the quickest and easiest way.

The Code below is from Debra's Contextures site:
Excel Macros for Pivot Table Pivot Cache - Lists, Info

Just put your cursor in a cell in the pivot you want to unlink.
Copy the macro into a module and run it.

' Contextures:- Create New Cache for Selected Pivot Table
Sub SelPTNewCache()
    Dim wsTemp As Worksheet
    Dim pt As PivotTable
    
    On Error Resume Next
    Set pt = ActiveCell.PivotTable
    
    If pt Is Nothing Then
        MsgBox "Active cell is not in a pivot table"
    Else
        Set wsTemp = Worksheets.Add
        
        ActiveWorkbook.PivotCaches.Create( _
            SourceType:=xlDatabase, _
            SourceData:=pt.SourceData).CreatePivotTable _
            TableDestination:=wsTemp.Range("A3"), _
            TableName:="PivotTableTemp"
        
        pt.CacheIndex = wsTemp.PivotTables(1).CacheIndex
        
        Application.DisplayAlerts = False
        wsTemp.Delete
        Application.DisplayAlerts = True
    End If
    
exitHandler:
        Set pt = Nothing

End Sub