15.3 Value Display Modes: % of Total, Running Total, & Differences

14.3 Value Display Modes: % of Total, Running Total, & Differences

While the Summarize Values By engine handles fundamental arithmetic aggregations, enterprise financial analysis demands relational intelligence. Executives rarely evaluate raw dollar totals in isolation; they require proportional shares, period-over-period variances, cumulative milestone tracking, and competitive rankings. The Show Values As engine inside Value Field Settings executes these advanced comparative calculations dynamically, eliminating the need to construct manual worksheet formulas outside the PivotTable.

---

The Mechanics of "Show Values As": Base Fields & Base Items

The Show Values As tab in the Value Field Settings dialog transforms raw aggregated numbers into relative analytical metrics. Right-clicking any value cell and navigating to Show Values As reveals fifteen specialized display modes:

Value Field Settings ──► [Show Values As Tab] ──► Select Calculation
                                                         │
                                    ┌────────────────────┴────────────────────┐
                                    ▼                                         ▼
                        [Global Calculations]                    [Contextual Calculations]
                        - % of Grand Total                       - % Difference From
                        - % of Column Total                      - Running Total In
                        - % of Row Total                         - Rank Largest to Smallest
                        (No Base Parameters)                     (Requires Base Field & Item)

Calculations fall into two structural classifications:

1. Global Proportional Calculations: Require no secondary parameters (e.g., % of Grand Total, % of Column Total, % of Row Total). Excel computes proportions against overall matrix boundaries.
2. Contextual Relational Calculations: Require the analyst to specify a Base Field (the dimensional axis across which the comparison is evaluated) and, in many cases, a Base Item (the reference benchmark against which all other items are compared).

---

Proportional & Share Analysis Calculations

Proportional modes express values as percentages of different structural totals across the PivotTable grid:

Nested Hierarchies: % of Parent Row Total

In a multi-level row hierarchy (such as Department > Category > Product), using % of Grand Total can dilute insights because individual product shares might register as tiny fractions (e.g., 0.15% of total company sales). Selecting % of Parent Row Total evaluates each item relative to its immediate parent subtotal:

• If the Electronics department generates $1,000,000 and its subcategory Laptops generates $650,000, Laptops displays 65.0%.
• The subcategories within each parent block sum to exactly 100%, allowing department managers to analyze internal category mix immediately.

---

Variance & Deviation Modeling: Difference From & % Difference From

Evaluating performance variances against historical benchmarks or baseline targets is a core requirement of executive reporting. Excel provides two dedicated modes:

• Difference From: Calculates the absolute mathematical variance: Value - Base Item Value.
• % Difference From: Calculates the percentage variance: (Value - Base Item Value) / Base Item Value.

Configuring the Base Field and Base Item

When configuring variance calculations, selecting the correct Base Item determines the analytical perspective:

1. Fixed Baseline Item: Choosing an explicit item (e.g., Base Field = Year, Base Item = 2024) measures all years against 2024. The 2024 row displays 0 (or 0.0%), while subsequent years show expansion or contraction relative to that base year.
2. Dynamic Relative Item (previous): Measures period-over-period changes (such as Month-over-Month or Year-over-Year). Each row compares itself to the preceding chronological row in the table. Because the initial row has no prior record, it displays #N/A or blank.
3. Dynamic Relative Item (next): Compares each record to the subsequent row.

Worked Analytical Model: Year-over-Year Revenue Variance

---

Cumulative Tracking: Running Total In & % Running Total In

Tracking cumulative progress toward annual targets requires calculating running accumulations down a timeline:

• Running Total In: Progressively sums values sequentially down a specified Base Field: Cumulative Sum = Previous Total + Current Item.
• % Running Total In: Expresses that running accumulation as a percentage of the overall total for the Base Field, culminating at 100% on the final record.

Exam Trap & Sorting Dependency: Running total calculations depend strictly on the visual sort order of the Base Field. If Month is sorted alphabetically (April, August, December...), Excel accumulates values in that chaotic alphabetical sequence! Analysts must ensure chronological fields are sorted sequentially (Jan, Feb, Mar...) for running totals to yield valid business intelligence.

---

Ordinal Ranking & Concentration Indexing

• Rank Smallest to Largest: Assigns ordinal ranking starting with 1 for the lowest numeric value within the Base Field (ideal for cost, defects, or golf scores).
• Rank Largest to Smallest: Assigns ordinal ranking starting with 1 for the highest numeric value (standard for sales leaderboards and profit rankings). Tied values receive identical ranks.
• Index: Calculates the relative concentration or weighting of a cell relative to row and column totals: $\text{Index} = \frac{\text{Cell Value} / \text{Row Total}}{\text{Column Total} / \text{Grand Total}}$ An index value equal to 1.0 denotes average performance. An index greater than 1.0 reveals a disproportionate concentration of revenue (e.g., an index of 1.45 indicates sales in that category/region cell are 45% higher than expected based on overall regional and categorical distribution).

---

Deploying Multi-Metric Side-by-Side Architectures

To build comprehensive executive dashboards, analysts frequently drag the same numeric field into the Values quadrant three times, configuring each instance with a distinct display mode:

1. Instance 1: Sum of SalesAmount ──► Summarize by Sum ──► Formatted as $#,##0 ──► Custom Name: "Total Revenue ".
2. Instance 2: Sum of SalesAmount ──► Show Values As: % of Column Total ──► Formatted as 0.0% ──► Custom Name: "% of Regional Sales".
3. Instance 3: Sum of SalesAmount ──► Show Values As: Running Total In (Base Field: Order Date) ──► Formatted as $#,##0 ──► Custom Name: "Cumulative YTD Sales".

This multi-display architecture provides absolute volume, relative regional mix, and cumulative trajectory within a single unified view—all powered natively by the PivotCache without external formulas.