CHAPTER 8:
VIEWING SPECIFIC
VALUES
1
Viewing Specific Values
■ Creating Crosstabs
■ Grand Totals, Sub-Totals, & Changing Aggregation
■ Practice: Totals & Aggregation
■ Creating Heat Maps
■ Creating Highlight Tables
■ Practice: Creating Highlight Tables
2
Creating Crosstabs (Text Tables)
■ Use when stakeholders need exact numbers
■ Rows: one or more dimensions
■ Columns: one or more dimensions
■ Text (Marks): one or more measures
■ Show Me → Text Table
3
Multi-Measure Crosstabs
■ Add multiple measures with Measure
Values/Measure Names
■ Measure Values → Text, Measure Names →
Columns (or Rows)
■ Remove unused measures from Measure Values
shelf
■ Align decimals, thousand separators, currency
4
Grand Totals & Sub-Totals
■ Analysis → Totals → Show Column/Row Grand
Totals
■ Add All Subtotals for hierarchical dimensions
■ Totals → Total All Using → SUM, AVG, MIN/MAX
(choose wisely)
■ Format totals differently (bold, borders)
5
Changing Aggregation
■ Default aggregation: SUM (can change per
measure)
■ Right-click Measure → Default Properties →
Aggregation
■ Per-view change: click the pill → choose
SUM/AVG/MIN/MAX/COUNT
■ Beware of table calcs vs base aggregation
6
Practice: Totals & Aggregation
(Quick)
1. Superstore → Orders.
2. Build crosstab: Rows: Category, Sub-Category; Columns:
Region; Text: SUM(Sales).
3. Analysis → Totals → Show Column & Row Grand Totals; Add
All Subtotals.
4. Change aggregation to AVG(Sales) (per-view) and compare.
5. Add Percent of Total (Quick Table Calc) on Sales by Table
(Across); duplicate sheet for SUM vs % comparison.
7
Heat Maps (Square Marks)
■ Purpose: show magnitude via color (and
optionally size)
■ Rows/Columns: dimensions; Color/Size: a
measure
■ Marks = Square; adjust Size slider
■ Diverging palette for centered metrics (e.g., Profit)
8
Creating Highlight Tables (Text +
Color)
■ Start from a crosstab (values visible)
■ Add measure to Color (Marks) → colors highlight
highs/lows
■ Keep Text labels to retain exact values
■ Center color at meaningful point (e.g., 0 for Profit)
9
Practice: Creating Highlight
Tables (Quick)
1. Rows: Sub-Category; Columns: Region; Text:
SUM(Profit).
2. Marks: Text → add SUM(Profit) to Color.
3. Color: Diverging, center at 0; show labels (Text) with
currency format.
4. Sort Sub-Category by Total Profit (desc).
5. Add Row Grand Totals to see contribution per Sub-
Category.
10
Best Practices & Pitfalls
■ Use Highlight Table when exact numbers matter;
Heat Map for denser grids
■ Avoid rainbow palettes; use sequential/diverging
with clear legend
■ Center at 0 for profit; use percent formats when
showing shares
■ Beware of very high cardinality (scrolling,
unreadable cells)
11
Quick Recap
■ Build crosstabs for precise values
■ Add totals/subtotals & choose the right
aggregation
■ Heat maps for magnitude; highlight tables for
numbers + emphasis
■ Practices reinforce totals and color-encoded tables
12