Concept
1. Open the settings
Right-click a number in the Values area → Value Field Settings. Two tabs:
- Summarize Values By — Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, Var.
- Show Values As — the calculations below.
Tip: drag Amount into Values twice — keep one as the plain number and change the second one to a %.
2. Summarize by
| Choice | Region North (practice data) |
|---|---|
| Sum | 5,09,000 |
| Count | 6 |
| Average | 84,833 |
| Max | 1,65,000 |
Distinct Count (e.g. number of different customers) appears only if you tick Add this data to the Data Model when creating the pivot (Module 4).
3. % of Grand Total
Region in Rows, Amount shown as % of Grand Total:
| Region | Sales | % of total |
|---|---|---|
| North | 5,09,000 | 47.6% |
| South | 2,59,000 | 24.2% |
| West | 3,02,000 | 28.2% |
4. % of Row / Column Total
With Category in Columns, % of Row Total answers "within North, what share is Electronics?" → North: Electronics 82.3%, Furniture 17.7%. West: 60.3% / 39.7%. % of Column Total answers "of all Electronics, how much came from North?"
5. % of Parent Row Total
Rows = Region, then Category (nested). Each category shows its share of its own region — North › Electronics 82.3%, North › Furniture 17.7% — and each region its share of the grand total. Ideal for hierarchies (Region › Store, Category › Product).
6. Running Total In
Rows = Date grouped by Month (Lesson 3). Show Values As → Running Total In → Base field: Date:
| Month | Sales | Running total |
|---|---|---|
| Apr | 4,08,000 | 4,08,000 |
| May | 3,80,000 | 7,88,000 |
| Jun | 2,82,000 | 10,70,000 |
"% Running Total In" shows the same as a share — useful for Pareto (top items making 80%).
7. Rank Largest to Smallest
Rows = Salesperson, Show Values As → Rank Largest to Smallest → Base field: Salesperson: Amit 1 (4,19,000), Ravi 2 (2,36,000), Sonal 3 (2,10,000), Neha 4 (2,05,000).
8. Difference From / % Difference From
Rows = Month. Difference From → Base field: Date, Base item: (previous):
| Month | Sales | Change vs previous | % change |
|---|---|---|---|
| Apr | 4,08,000 | ||
| May | 3,80,000 | −28,000 | −6.9% |
| Jun | 2,82,000 | −98,000 | −25.8% |
Base item can also be a fixed item (e.g. compare every region with North).
9. Index (advanced)
Shows how much a cell is above/below what you'd expect from its row and column totals. Rarely needed; good for spotting unusual combinations.
Common mistakes
Changing the only Amount field to % and losing the actual numbers (add it twice). Wrong base field in Running Total (must be the field in Rows that represents time). Expecting Distinct Count without the Data Model.