Broken refresh, a misleading calculated field and slicers that only move one table

Cause. The pivot cache stores a hard address, Raw!$A$1:$M$250001. Refresh re-reads exactly that rectangle and never looks below it, so the new invoices are outside the source and invisible no matter how often someone clicks Refresh.

Options, best first:

1. Convert the source to an Excel Table (Ctrl+T, name it tblInvoices) and set PivotTable Analyze > Change Data Source to tblInvoices. Rows pasted immediately beneath the table join it on paste, so Refresh includes them with no address to maintain. This is the durable fix for a monthly-growing file.
2. Change Data Source each month to the new last row. It works, but it is a manual step that three different analysts will eventually forget.
3. A dynamic defined name as the source also works, but it is harder for the next analyst to audit than a named table.

Also set PivotTable Options > Data > Refresh data when opening the file, which is what makes Monday's view correct. Warn the team that a full refresh over 250,000 rows takes time, and that on a shared drive two people refreshing simultaneously will fight over the same file; a copy each is safer than a shared live workbook.

Mechanism. A calculated field is not evaluated row by row. Excel first aggregates each underlying field across the cell's scope - Sum of Revenue, Sum of Deals - and only then applies the formula to those totals. =Revenue/Deals therefore computes total revenue divided by total deal count, a weighted figure dominated by the largest invoices. On skewed data that lands at 41,000 while the mean invoice is 1,730. The same trap makes any "average of a percentage" calculated field meaningless, because it becomes a ratio of sums.

Correct constructions:

1. Add a column to the source - =Revenue/Deals per row - then place that field in Values with the aggregation set to Average. That averages the row-level metric, which is what finance actually means. It also inherits any new rows if the source is a table.
2. If the question is simply average revenue per deal, drag Revenue to Values twice: one instance Sum, one instance Average. No calculated field is needed.
3. In Power Pivot, write a true measure, and note that SUM([Revenue])/SUM([Deals]) is still a ratio of sums; AVERAGEX is the row-level equivalent.

Test before shipping: reconcile against =AVERAGEIFS(Revenue, ...) over the source for one region and one month.

The #REF! cause. GETPIVOTDATA stores field and item names as text at the moment the formula is generated, for example =GETPIVOTDATA("Sum of Sales",$A$3,"Salesperson","North"). Renaming the field to Rep, or removing it from the pivot, invalidates that name and the formula errors. A layout change from Compact to Tabular is normally harmless on its own; the rename is the culprit, which is why the two events looked like one.

Making it resilient:

• Treat field names as part of the interface and keep them stable; if a rename is unavoidable, Find & Replace "Salesperson" across the dashboard sheet.
• Drive the criteria from cells - =GETPIVOTDATA("Sum of Sales",$A$3,"Rep",$B$1) - so a picker selects the item rather than a hard-coded string.
• If reference-safety is not needed, turn off PivotTable Analyze > Options > Generate GetPivotData and use direct cell references; shorter, but they break on sort and layout changes.

Slicers. One slicer filters one PivotTable by default. Right-click the slicer > Report Connections and tick all four. They only appear in that list if they share the same pivot cache, meaning the same source range; if one PivotTable was built from an older range it will be missing, and the fix is to rebuild it from the shared source rather than to add a second slicer. Recheck Report Connections after any Change Data Source.