# Wrong grand total when using Calculated Field

I have a simple pivot table, but I'm using Calculated Field values.

The raw data used for the pivot table is like a transaction log, as shown below:

DATE STOCK QTY PRICE TOTAL
03/2023 AAPL 1 100 100
03/2023 AAPL 2 120 240
03/2023 GOOG 2 60 120
03/2023 GOOG 3 80 240

The sheet also has the current price of a ticker with the `GOOGLEFINANCE(..)` function, as shown below:

TICKER PRICE
AAPL 158.28
GOOG 103.06

The pivot table groups the transactions by the ticker name, sums up the `TOTAL` per transaction `QTY * PRICE`, and shows the current value `QTY * 'CURRENT PRICE'`. The current price is a lookup to the current price table expressed as `=VLOOKUP(STOCK,\$K\$7:\$L\$10,2, FALSE)*SUM(QTY)` in the pivot table as a calculated field.

However, the Grand Total of the `CURR POSITION` column shows a wrong value. It shows `\$3,044.75` instead of `\$1,150.88`. It should sum the `VLOOKUP` value, but it shows a different value.

How can I fix this?

Sample sheet for better visualization here

And prints bellow:

• Totals would likely be better at the top or somewhere with easy access imo. For ex, if you have 100 stocks, how will you be able to quickly glance at overall performance? Scroll down 100 rows? Commented Mar 29, 2023 at 5:10

You can get the desired result by using a `query()` formula instead of a pivot table, like this:

``````=let(
transactions, B6:E16,
tickers, offset(transactions, 0, 0, , 1),
quantity, offset(transactions, 0, 1, , 1),
position_, lambda(t, q, q * vlookup(t, K7:L10, columns(K7:L10), false)),
totals_, lambda(col, if(sum(col), to_dollars(sum(col)), "Grand Total")),
table, query(
{ transactions, map(tickers, quantity, position_) },
"select Col1, sum(Col4), sum(Col5)
where Col1 is not null
group by Col1
label sum(Col4) 'Invested', sum(Col5) 'Current position' ",
1
),
{ table; bycol(table, totals_) }
)
``````

Another workaround would be to re-calculate the totals in the pivot table like this:

``````=let(
pivot, query( { G6:I13 }, "where Col1 is not null and Col1 <> 'Grand Total' ", 1),
totals_, lambda(col, if(sum(col), to_dollars(sum(col)), "Grand Total")),
{ pivot; bycol(pivot, totals_) }
)
``````

See query(), let(), query(), bycol() and the sample spreadsheet.