Excel Lesson 35 – Measures | Dataplexa

Lesson 35 · Power Pivot Practical

Measures in Power Pivot

Measures are the output layer of a Power Pivot model — the numbers that appear in PivotTable values, KPI cards, and charts. A well-designed measure library turns a raw data model into a self-service analytics tool: anyone can drag measures onto a PivotTable and get correct, context-aware numbers without needing to understand the underlying data structure. This lesson covers how to organise and format measures, build KPI measures, use DAX for conditional colour logic, create dynamic measure selection, and design a complete measure library that is ready for a production dashboard.

Organising Measures — The Measure Table Pattern

By default, measures live in whichever table you created them in. In a model with many measures across multiple tables, this quickly becomes hard to navigate. The professional approach is to create a dedicated empty table — often called "Measures" or "_Measures" (the underscore keeps it at the top of the field list) — and house all measures there. The table contains no data; it exists purely as a container.

Creating a dedicated Measures table:

Method 1 — Enter Data (simplest):
  Power Pivot → Manage → Home → Get External Data → From Other Sources
  → Text file → or use:
  In Excel: Insert → Table with one column and one row (type "Placeholder")
  → Power Pivot tab → Add to Data Model
  → Rename the table "_Measures"
  → Delete the Placeholder column (the table remains as an empty container)

Method 2 — DAX table:
  Power Pivot → New → enter a DAX expression:
  _Measures = ROW("Placeholder", BLANK())
  Then hide the Placeholder column

Moving a measure to a different table:
  Right-click the measure in the calculation area
  → Move to Table → select _Measures

Why this matters:
  The field list in PivotTable shows tables as expandable groups.
  Keeping all measures in _Measures makes them easy to find —
  they appear together at the top, separated from dimension columns.

Field List — Organised Model vs Unorganised Model

❌ Measures scattered across tables

▶ Sales

OrderID · Revenue · Cost
Σ Total Revenue
Σ Profit Margin %

▶ Customers

CustomerName · Region
Σ Unique Customers

▶ DateTable

Date · Year · Month
Σ Revenue YTD
Σ Revenue LY

✅ All measures in one place

▶ _Measures

Σ Total Revenue
Σ Profit Margin %
Σ Unique Customers
Σ Revenue YTD
Σ Revenue LY
Σ YoY Growth %

▶ Sales

OrderID · Revenue · Cost

▶ Customers

CustomerName · Region

Formatting Measures

Every measure can be given a default number format so it displays correctly whenever it appears in a PivotTable — currency symbols, decimal places, percentages, or thousands separators — without needing to format the PivotTable cell manually each time.

Setting measure format in Power Pivot:
  Click the measure in the calculation area
  Home tab → Formatting group → choose format type
  Or: right-click measure → Format

Common formats to set:

Currency:
  Category: Currency
  Symbol: £ (or $ € ¥ etc)
  Decimal places: 0 (for whole pounds) or 2

Percentage:
  Category: Percentage
  Decimal places: 1 or 2
  (DAX stores as decimal — 0.342 — format displays as 34.2%)

Whole number with thousands separator:
  Category: Number
  Decimal places: 0
  Use 1000 separator: Yes

Custom format string (advanced):
  In Power BI / newer Excel versions you can also use format strings like:
  £#,##0     → £312,000
  0.0%       → 34.2%
  #,##0;[Red]-#,##0   → positives normal, negatives in red

Setting formats on measures saves significant time in dashboard building. When a measure has its format baked in, dropping it onto any PivotTable automatically shows the right symbol and decimal places — no reformatting step needed. For a model with twenty measures, this is the difference between a five-minute formatting job and a one-second one.

KPI Measures — Status and Goal Tracking

A KPI (Key Performance Indicator) in Power Pivot is a measure with a defined target and status thresholds — the green/amber/red traffic light logic that tells users at a glance whether performance is on track. Power Pivot has a built-in KPI feature, but the more flexible approach is to build KPI status measures in DAX directly, which gives you full control over the thresholds and labels.

KPI pattern — Revenue vs Target:

Revenue Target := SUM(Targets[RevenueTarget])
  (from a Targets dimension table connected to the model)

Revenue vs Target :=
  VAR Actual = [Total Revenue]
  VAR Target = [Revenue Target]
  RETURN DIVIDE(Actual - Target, Target, BLANK())

Revenue Status :=
  VAR Pct = [Revenue vs Target]
  RETURN
    IF(ISBLANK(Pct), BLANK(),
    IF(Pct >= 0.05,   "✅ On Track",
    IF(Pct >= -0.05,  "⚠️ At Risk",
                      "❌ Behind"
    )))

Budget Attainment % :=
  DIVIDE([Total Revenue], [Revenue Target], 0)

These three measures together give:
  - The % gap vs target (Revenue vs Target)
  - A traffic light label (Revenue Status)
  - A budget attainment % for column charts (Budget Attainment %)

KPI Summary Table — Status by Region

Region	Actual	Target	vs Target	Status
North	£619,000	£580,000	+6.7%	✅ On Track
East	£560,000	£570,000	-1.8%	⚠️ At Risk
West	£441,000	£500,000	-11.8%	❌ Behind

Status column generated entirely by DAX — thresholds adjustable in one place by editing the Revenue Status measure

Dynamic Measure Selection — Switching Metrics With a Slicer

A powerful dashboard pattern lets users pick which metric to display using a slicer — switching between Revenue, Profit, and Order Count without changing the PivotTable layout. This requires a disconnected parameter table and a single switching measure that reads the slicer selection.

Step 1 — Create a disconnected parameter table (no relationships):
  MetricSelector table:
  MetricID | MetricName
  1        | Revenue
  2        | Profit
  3        | Order Count

  Load to Data Model. Do NOT create a relationship to any other table.

Step 2 — Create the switching measure:
Selected Metric :=
  VAR Selection = SELECTEDVALUE(MetricSelector[MetricName], "Revenue")
  RETURN
    SWITCH(Selection,
      "Revenue",     [Total Revenue],
      "Profit",      [Gross Profit],
      "Order Count", [Order Count],
      [Total Revenue]   -- default fallback
    )

Step 3 — Add a slicer on MetricSelector[MetricName]
  The slicer has three buttons: Revenue | Profit | Order Count
  Clicking one changes what Selected Metric returns
  The PivotTable, chart, and KPI card all update together

SELECTEDVALUE(column, default):
  Returns the single selected value in the column if exactly one is selected
  Returns the default value if none or multiple are selected

Dynamic Metric Selector — One PivotTable, Three Metrics

Metric Slicer

Revenue

Profit

Order Count

Region	Selected Metric = Revenue
North	£619,000
East	£560,000
West	£441,000

Click "Profit" → column shows Gross Profit per region · Click "Order Count" → shows order counts · One PivotTable, three metrics

Section V Summary — Power Pivot and DAX in Five Lessons

You have now completed the full Power Pivot and DAX section. Here is a quick reference for what each lesson introduced and the capabilities it unlocked.

Lesson	Topic	Key capability unlocked
31	Power Pivot Basics	Enabling, loading data, the model, relationships, star schema
32	Data Models	Filter context, Date Table, calculated columns vs measures, RELATED
33	DAX Basics	CALCULATE, ALL, FILTER, COUNTROWS, DISTINCTCOUNT, operators
34	DAX Advanced	VAR/RETURN, time intelligence, DATEADD, RANKX, SUMX iterators
35	Measures	Measure tables, formatting, KPI measures, dynamic metric switching

💡 Teacher's Note

The dynamic metric selector pattern is one of the most impressive things you can demonstrate in Excel to a non-technical audience. People who are used to static spreadsheets are genuinely surprised when they see a chart title, a table column header, and the underlying numbers all change simultaneously from a single slicer click — with no VBA, no macros, and no formulas on the sheet. It is pure DAX and model design. Building this kind of interactivity is the goal to aim for: a model that empowers users to explore the data themselves rather than asking you to rebuild the report every time the question changes.

🟠 Practice

Q1. You have a measure called Total Revenue and a Targets table with a RevenueTarget column. Write a DAX measure called "Budget Attainment %" that divides Total Revenue by the sum of RevenueTarget, returning 0 if target is zero.

Q2. You have a MetricSelector table with MetricName values of "Revenue", "Profit", and "Units". Write the key line inside the switching measure that reads the slicer selection and defaults to "Revenue" if nothing is selected.

Q3. Why should the MetricSelector table have no relationship to any other table in the data model?

Next up — Dashboard Basics, where we move into Section VI and start building the visual layer — learning how to design an Excel dashboard layout, choose the right chart types, and plan a dashboard that communicates clearly to its audience.

← Previous Course Index Next →

Excel Course