Pivot Table Calculated Fields in Excel: Fix Wrong Totals

Harrowgate Catering Supplies delivers dry goods and frozen stock to care-home groups across West Yorkshire out of a depot in Stourton. Six customers, all of them on a volume rebate, and the rebate is the only complicated thing in the business: a percentage of net sales, agreed per customer, paid quarterly in arrears. Dearne Valley Trust, the biggest account, is on 1.5%. Calder Vale Lodges, the smallest, is on 3.0%. The rate lives on the invoice line, because the billing system writes it there.

Q1 FY26 — April, May and June 2026 — came to 46 invoices on a sheet called Sales: customer in column A, month in column B, net sales in column C, rebate rate in column D. Net sales £647,250.00. The rebate those contracts describe is £12,687.00.

The management accountant built a PivotTable: customers down the side, months across the top, Sum of Net Sales in the values. Then, because the source had no rebate column and she needed one, PivotTable Analyze ▸ Fields, Items, & Sets ▸ Calculated Field:

Name: Rebate Formula: ='Net Sales'*'Rebate Rate'

Sales times rate. It is what the contract says in English and it is what the formula says in Excel, and the twelve monthly cells it produced were journalled into the accrual month by month. Those twelve cells add up to £42,050.50.

Somebody did check it. Four cells were traced back to the invoice and all four tied to the penny.

Where the £29,363.50 Came From

A calculated field is not a row-level formula. It adds the fields up first, inside whatever cell you are looking at, and applies the formula to the sums afterwards.

So the cell where Ashfield Care Group meets April does not multiply each of that month's three invoices by 2.5% and add the results. It adds the three net-sales figures — £42,000.00 — and it adds the three rate figures — 2.5% + 2.5% + 2.5% = 7.5% — and multiplies those. £3,150.00 against a true £1,050.00. Three invoices, three times too much.

That is the whole fault, and its shape is what made it survive:

• The factor is the invoice count, so it is different in every cell. Three-invoice cells were 3× out, five-invoice cells 5× out. Nothing in the report was a round multiple of anything, which is exactly what a reviewer looks for.
• Cells with one invoice were right. Elmsall House billed once in April and once in May; Brinsworth Homes once in April; Calder Vale once in May. On a single row, adding then multiplying and multiplying then adding are the same arithmetic. Those were four of the cells that got checked, and a fifth was drilled into down to a single invoice by using the filter — which made it right while it was being looked at.
• The totals disagreed with each other, in the same pivot, at the same moment. The twelve cells add to £42,050.50. The six customer subtotal rows read £121,996.50, because each one is the formula applied to that customer's own sums. The Grand Total cell read £624,596.25, which is £647,250.00 × 96.5% — and 96.5% is every rate on all 46 rows added into one number.

Nobody read the Grand Total. The accrual was posted per customer-month, from the body of the pivot, so the one figure absurd enough to stop the process was the one figure the process never used.

What It Cost

The £29,363.50 was found in the July close, when the first rebate was actually paid and the credit notes came to £12,687.00. It was reversed in one journal.

What did not reverse: Dearne Valley Trust's 2026–27 renewal was priced in June on cost-plus, and the cost side carried Q1's accrued rebate of £20,352.00 against a true £4,335.00 — 5.5 points of margin that did not exist. Harrowgate quoted 2.1% up on a contract worth £1.16m a year and lost it by 1.4%. The arithmetic was reversed in July. The account was not.

1) One Rebate, Eight Numbers

Three of these eight figures are the rebate: the SUMPRODUCT, the helper column, and the single-invoice cell. The other five are the same calculated field, read at five different levels, each of them a correct evaluation of a formula that was never the contract.

🎯 Scenario: Open any pivot that has a calculated field in it and put =SUM(...) under its value area, over the body cells only. Compare that to the pivot's own Grand Total cell. For Sum of anything they match. If they do not match, the field is not additive, and every subtotal in that report is a number you have not checked.

2) What a Calculated Field Actually Computes

Excel's documentation puts it in one line: a calculated field's formula operates on the sum of the underlying data for every field it names. Not the rows. The sums.

Written as a formula over the rows behind a cell, what you asked for and what you got are these two:

• What the contract says: =SUMPRODUCT(net, rate) — multiply each row, then add.
• What the calculated field does: =SUM(net) * SUM(rate) — add each field, then multiply.

For one row they are identical. For more than one row they are different, and the gap is not a rounding difference, it is a different quantity. This is the same arithmetic the SUMPRODUCT article is built on, approached from the other side: sum-then-multiply and multiply-then-sum are two operations, and only one of them is the money.

Two consequences follow immediately, and both of them are in the table above.

1. The result depends on how many source rows sit behind the cell, which depends on the layout, the filters and the slicers. Change nothing but the Month filter and the number changes by a factor.
2. There is no level at which Excel sums the cells. A subtotal is the formula on the subtotal's sums; the Grand Total is the formula on the grand totals. There is no setting that makes a calculated field add up the cells above it, because the cells above it are not quantities it is made of.

🎯 Scenario: In a spare cell beside the pivot, divide the calculated-field cell by the Sum of Net Sales cell next to it. That is the rate the pivot thinks it is applying. On this report it came out 7.5%, 6.0%, 10.0% and 7.5% on four adjacent cells of a contract whose highest rate anywhere is 3.0%.

3) Where a Calculated Field Is Right: Ratios

None of this makes the feature broken. For the thing it is for, a calculated field is not only right, it is more right than the alternative.

A margin percentage is a ratio of two sums. =Margin/'Net Sales' in a calculated field returns £12,400.00 of margin over £128,800.00 of sales, which is the weighted margin — 9.63% — at every level of the report, because at every level it divides that level's margin total by that level's sales total. Grand Total included. That is the correct answer and it is correct for the same reason the rebate was wrong: division of sums is a weighted average, and multiplication of sums is nothing at all.

Compare the two ways people try to get a margin percentage into a pivot:

• Calculated field =Margin/'Net Sales' — weighted, consistent at every level, right.
• A Margin % column in the source, summarised as Average — the unweighted mean of the row percentages. A £40.00 invoice at 60% and a £40,000.00 invoice at 8% average to 34%, and the business made 8.05%.

So the rule that comes out of it is not "avoid calculated fields". It is narrower and easier to apply:

A calculated field is safe when the formula is a ratio of fields, and unsafe when it multiplies two fields, or divides by a count, or needs a row's own value. Division of sums is the weighted figure people want. Multiplication of sums is an accident.

🎯 Scenario: List every calculated field in the pack — section 7 shows how to get them out of Excel in one click — and sort them into the two piles. Ratios stay. Anything with a * between two field names, or any arithmetic that only makes sense on one invoice, becomes a source column this week.

4) The Three Totals, and Why They Disagree

This is the part that is worth being able to say out loud in a meeting, because a pivot with a non-additive calculated field in it hands you three different answers and labels all three of them the same way.

On Harrowgate's report, for Dearne Valley Trust:

The customer row is not the sum of the three cells and was never going to be. It is the formula applied to 14 invoices' worth of sums, with 14 rates in it. Four numbers, one row of a report, and the only one that appears nowhere on the pivot is the one that is owed.

An additive field has none of this. Sum of Net Sales down that row is £96,500.00 + £88,200.00 + £104,300.00 = £289,000.00, the customer row says £289,000.00, and the Grand Total is the sum of the customer rows. Additivity is the property that lets you read a pivot by eye, and a calculated field quietly takes it away.

🎯 Scenario: Take any pivot in a monthly pack, add the body cells of each row with =SUM() and put the pivot's own subtotal beside it. On additive fields the difference column is a column of zeros, and a column of zeros is a thing you can show somebody. Anything else is a field that needs section 5.

5) The Fix Is a Column in the Source

The rebate is a row-level quantity. It exists on an invoice, one invoice at a time, and the right place for it is on the invoice row.

In Sales, column E:

E1: Rebate Due
E2: =C2*D2

Fill it down the 46 rows, refresh the pivot, drop Sum of Rebate Due into the values and delete the calculated field. The number is now £12,687.00 in the Grand Total, £12,687.00 as the sum of the twelve cells, £12,687.00 as the sum of the six customer rows and £12,687.00 in a cell on the source sheet that reads =SUM(E2:E47). Four agreeing numbers, because Sum is additive and arithmetic done per row stays done.

Three ways to get that column, in increasing order of how much you will like yourself later:

1. A formula in the sheet, as above. Thirty seconds. It has to be re-filled when rows are appended, which is what Excel Tables are for — inside a Table, =[@[Net Sales]]*[@[Rebate Rate]] fills itself on every new row.
2. A Power Query step. If the sales data is loaded rather than typed, Add Column ▸ Custom Column with [Net Sales] * [Rebate Rate] does it on every refresh, and nobody can forget it.
3. A measure, if the pivot is on the Data Model. Rebate:=SUMX(Sales, Sales[Net Sales] * Sales[Rebate Rate]) is the row-by-row multiplication done properly, at every level, without a helper column. SUMX iterates the rows — it is the DAX answer to exactly this problem, and it is why Data Model pivots have no Calculated Field option at all.

🎯 Scenario: When you add the helper column, keep the calculated field for one refresh and put both in the values area side by side. The two columns disagreeing in front of you, cell by cell, is worth more than any explanation of why — and it tells you how long the accrual has been wrong, because the ratio is the invoice count.

6) Calculated Items, and the Grand Total That Counts Them

A calculated item is a different thing with a similar name. A calculated field invents a new field; a calculated item invents a new member of a field you already have — a thirteenth month, a fourth region, a budget line that is two other budget lines added together.

Click a cell in the Month field, Fields, Items, & Sets ▸ Calculated Item, and the formula is written in item names:

Name: Q1   Formula: =Apr+May+Jun

The column appears, it reads £647,250.00, and it is correct. Then look at the Grand Total: £1,294,500.00.

Nothing is broken. The Grand Total adds up the members of the Month field, and the Month field now has four members. Q1 is counted as a month alongside the three months it is made of, so the total is exactly double. On a report where the calculated item covers only part of the field — =Jan+Feb on a twelve-month pivot — the overstatement is not double, it is 2/12 of the year, which is far harder to see and has been sitting in some workbooks for years.

What to do instead, in order of preference:

• Put a Quarter column in the source and use it as a field. A real column is filterable, groupable, and sums correctly.
• Group the dates. Right-click a date field ▸ Group ▸ Quarters gives real quarter members, with no double counting. (And once a field is grouped, Excel will not let you add a calculated item to it at all — you have to ungroup first, which is the software telling you something.)
• Use Subtotals, if what you wanted was a line under each group rather than a new member.

Calculated items have two more edges worth knowing before you use one:

• They write a formula into every cell of that item across the whole report, and you can then overwrite a single cell's formula, which gives you a pivot where one cell is special and nothing says so.
• They are positional-friendly but name-fragile: =Apr+May+Jun stops meaning anything the month a source system starts writing APR, and an item that disappears from the data takes its arithmetic with it.

🎯 Scenario: In any pivot you inherited, click the field buttons and look for an item whose name is a period or a grouping rather than a value from the data — Q1, Total, Other, H1. Then compare the Grand Total to =SUM() over the member cells. If it is bigger, you have found a calculated item and the size of the double count.

7) Solve Order and List Formulas

Two buttons under Fields, Items, & Sets that almost nobody opens, and both of them are for the situation above.

List Formulas writes a new worksheet containing every calculated field and calculated item in the pivot, its formula, and its solve order. It is the only way to read a pivot's arithmetic without clicking through dialogs, it takes one click, and a copy of it belongs in any pack that contains a pivot somebody else built. Nothing in the workbook records these definitions anywhere else — delete the pivot and the formulas are gone with it.

Solve Order matters when a calculated field and a calculated item cross. The cell where the Rebate field meets the Q1 item can be computed two ways — the field's formula on Q1's sums, or the item's formula on the field's cells — and they are not the same number. Excel resolves it by solve order: the last formula in the list wins for the cells where they intersect. Move a formula up or down the list and a cell's value changes, with no trace of why on the face of the report.

🎯 Scenario: Run List Formulas on every pivot in next month's pack before you send it, and read the sheet. Two lines and a solve order is a healthy pivot. Six calculated items referring to each other is a report that needs rebuilding in the source, and you now have the evidence on a sheet instead of in an argument.

8) Show Values As Is Not a Calculated Field

A good share of the calculated fields in circulation are doing a job the pivot does natively and better, under Value Field Settings ▸ Show Values As:

• % of Grand Total, % of Column Total, % of Row Total — no calculated field needed, and no double counting.
• % of Parent Row Total — the share of the subtotal above it, which is laborious to build any other way.
• Running Total In — a cumulative total down any field, which as a calculated field is not possible at all.
• % Difference From — month on month, or against a baseline month, which is the growth column people build with three helper pivots.
• Rank Largest to Smallest — a rank inside each group, live, that re-ranks when the filter changes.

These are display calculations: they recompute with the filter, they never add a member to a field, and they cannot be journalled by accident because they come out of the report as percentages. If a calculated field's formula divides one thing in the pivot by another thing in the pivot, Show Values As is probably the feature you wanted.

🎯 Scenario: Before adding a calculated field, open Value Field Settings on the number you were going to divide and read the Show Values As list top to bottom. It is thirteen entries long and most people have never read it.

9) What a Calculated Field Cannot Do

It is a small language, and the boundaries are not where people expect.

• It cannot see a cell. No $B$1 for an exchange rate or a threshold; no reference to a cell anywhere on any sheet. A constant must be typed into the formula, where it is invisible until somebody runs List Formulas.
• It cannot see a range, so every function that takes one is out: no SUMIFS, no COUNTIF, no VLOOKUP, no XLOOKUP, no FILTER.
• It cannot count rows. There is no COUNT of the source behind a cell, so "average per invoice" cannot be written as a calculated field — it needs a Count value field beside the Sum, and a formula outside the pivot, or a measure.
• It cannot read another cell of the pivot, including the Grand Total. A share-of-total is Show Values As, not arithmetic.
• It lives in the values area and nowhere else. You cannot put a calculated field in Rows, Columns or Filters, and you cannot slice on it: there is no list of its values to filter by, because it has no values until a cell exists.
• It cannot be drilled into. Double-click Sum of Net Sales and Excel shows you the invoices behind it. There are no invoices behind Rebate — no row of the source holds that number — so there is nothing to show.
• It cannot take a name already used by a field in the source, which is a small mercy: it stops Rebate Rate meaning two things in one report.
• It does not exist on a Data Model pivot. Add the table to the Data Model, or build the pivot from Power Pivot, and Calculated Field and Calculated Item are greyed out. The replacement is a DAX measure, which is strictly more capable — SUMX does the row-by-row multiplication a calculated field cannot — and which is also the reason Microsoft stopped developing this corner.

🎯 Scenario: If a calculated field you are about to write needs a constant, a lookup, a count or another cell of the pivot, stop: it is a source column or a measure. The list above is not a list of limitations to work around. It is a list of signals that the arithmetic belongs somewhere else.

10) Reconciling a Pivot From Outside It

A pivot proves nothing about itself. The check has to come off the source rows, with arithmetic you wrote.

With the 46 invoice rows in A2:D47, customers down column H from H2 and months across row 1 from I1:

=SUMPRODUCT(($A$2:$A$47=$H2)*($B$2:$B$47=I$1)*$C$2:$C$47*$D$2:$D$47)

That is the rebate for one customer-month, multiplied row by row and then added — the contract's order of operations, in one cell, filled across twelve cells. Put it next to the pivot and the £29,363.50 is visible in under a minute, cell by cell, with the biggest gaps where the invoice counts are highest.

Three more checks that each take one cell:

• =SUMPRODUCT(C2:C47,D2:D47) — the whole quarter's rebate, £12,687.00, against the pivot's Grand Total.
• =SUM(D2:D47) — the rate column added up, 96.5%. A rate column has no legitimate total; seeing one printed is the fastest way to recognise what the Grand Total cell did.
• =COUNTIFS($A$2:$A$47,$H2,$B$2:$B$47,I$1) — the invoice count behind each cell, which on a multiplying calculated field is also the factor by which that cell is wrong.

And if you need the pivot's own number inside a reconciliation, GETPIVOTDATA will give it to you faithfully — including the arithmetic. =GETPIVOTDATA("Rebate",$A$3,"Customer","Dearne Valley Trust") returned £60,690.00 on this report: the right cell, retrieved correctly, and fourteen times the money, because fourteen invoices sit behind it.

🎯 Scenario: Any pivot figure that becomes a journal entry needs a source-row formula beside it and a difference cell between them. Not a sample, not a spot-check of four cells — a formula, over the same rows, written a different way. That is the only test the £42,050.50 would have failed.

11) Six Checks Worth One Cell Each

1. =SUM(body cells) against the pivot's own Grand Total. Equal means additive. Unequal means read section 4 before you use either number.
2. =SUMPRODUCT(value_col, rate_col) on the source against the pivot's total, for any field that multiplies.
3. The implied rate: calculated-field cell ÷ Sum of Net Sales cell. Anything above the highest rate in the contract is arithmetic, not a discount.
4. =MAX(D2:D47) — the highest rate that exists. Pin it above the pivot. It is the ceiling nothing in the report may exceed.
5. =COUNTIFS(...) per cell — the invoice count, which is the error factor on a multiplying field, and 1 wherever the field happens to be right.
6. List Formulas, once per pivot, filed with the pack. If it comes back with items as well as fields, compare the Grand Total to the sum of the members before anybody reads the report.

🎯 Scenario: Checks 1, 2 and 4 take about two minutes on a report you already produce. Run them on last quarter's pack before you run them on this quarter's, because the question that follows is always "how long has this been wrong", and the answer is in the ratios.

12) Twelve Traps

1. A calculated field adds first and applies the formula second. ='Net Sales'*'Rebate Rate' is SUM(net)*SUM(rate), not SUMPRODUCT(net,rate).
2. It is right wherever a cell covers one row, which is why spot-checks pass. Check a cell with several invoices in it, deliberately.
3. A subtotal is not the sum of the cells above it. For a non-additive field it is the formula on that level's sums, and the two differ by whatever the data does.
4. The Grand Total is the formula on the grand totals. £647,250.00 × 96.5% is not a rebate; it is two unrelated sums multiplied.
5. The error factor is the row count, so it is different in every cell and never looks like a systematic error.
6. The filter changes the answer. Filtering down to one invoice makes a multiplying field correct, which is the worst possible behaviour during a review.
7. Ratios of fields are fine and are what the feature is for. =Margin/'Net Sales' is weighted and consistent at every level.
8. An Average of a percentage column is not a weighted average. A £40.00 invoice counts as much as a £40,000.00 one.
9. A calculated item is a new member of a field, and the Grand Total counts members. =Apr+May+Jun doubles the year. Group the dates or add a Quarter column instead.
10. Solve Order decides what happens where a field and an item cross, and the last formula in the list wins. Nothing on the report says so.
11. Nothing records these definitions but the pivot itself. Run List Formulas and keep the sheet, or the arithmetic leaves with the pivot.
12. Data Model pivots have neither feature. Write a measure — SUMX for the row-by-row case — and a Data Model pivot is where this problem stops existing.

Harrowgate still reports the rebate out of a PivotTable, monthly, in the same workbook.

What changed on Sales is one column. Column E holds =C2*D2 on every invoice row, the pivot shows Sum of Rebate Due, and the calculated field is gone. Above the pivot sit three cells: =SUMPRODUCT(C2:C47,D2:D47), the pivot's Grand Total fetched with GETPIVOTDATA, and the difference between them, which has read £0.00 since the July close. A fourth cell holds =MAX(D2:D47) under the label "no cell below may imply a rate above this".

The general lesson is not about a dialog box. It is that a pivot aggregates before it calculates, and arithmetic that must happen per row has to happen before the pivot sees the data. The calculated field was not a mistake in the sense of a typo. It was a correct evaluation of SUM(net) × SUM(rate) at every level of the report, printed in a column headed Rebate, and the only thing wrong with it was that nobody had asked Excel for a rebate.