Core DAX Measures for KPIs

Before adding the new measures, it is worth understanding the structure of what has already been built. Total Sales and Transaction Count are base measures — each calculates one thing directly from the data with no dependency on other measures. Total Quantity and Distinct Customers are also base measures. The only composite measure in this chapter is Average Order Value — it references Total Sales and Transaction Count by name rather than repeating their calculations.

Distinct Customers := DISTINCTCOUNT(Sales[Customer ID])

Counts the number of unique Customer ID values in the Sales table — not in the Customers dimension table. This ensures only customers with actual sales activity in the current filter context are counted. A customer who exists in the Customers table but placed no orders is correctly excluded.

Average Order Value := DIVIDE([Total Sales], [Transaction Count])

Divides Total Sales by Transaction Count using the DIVIDE function rather than the / operator. References the two base measures by name using square brackets with no table prefix — the DAX signal that a measure (not a column) is being referenced.

Total Quantity := SUM(Sales[Quantity])

Sums the Quantity column — the number of units sold rather than the revenue generated. Revenue and quantity often move together, but not always. Tracking both allows quick identification of periods with many low-value transactions versus fewer high-value ones.

DISTINCTCOUNT vs. COUNTROWS

Using COUNTROWS to count customers gives the wrong answer whenever a customer has placed more than one order. For a customer who placed three orders, COUNTROWS counts three customers. DISTINCTCOUNT counts one. For any question phrased as "how many customers", always use DISTINCTCOUNT.

Referencing

DAX uses a consistent visual signal to distinguish between columns and measures in a formula:

Validating the Measures Before Use

After writing all five measures, the recommended validation step is to add them all to a pivot table values area without any row or column breakdown first. The grand total row shows all five measures calculated against the entire dataset. Check whether each number is plausible — if Total Quantity is lower than Transaction Count, for example, something is likely wrong. Only after the totals look reasonable should rows and slicers be added.

1. Insert a pivot table from the Sales table, add to data model;
2. Drag all five measures into the Values area — no rows or columns yet;
3. Check that each grand total looks plausible against what you know about the data;
4. Add Region from Customers to rows — verify each region shows a reasonable breakdown;
5. Add a Category slicer from Products — confirm all five measures respond correctly to filtering.

Task

In this task you will add three new measures to complete the five-measure KPI toolkit, then validate all five in a multi-dimension PivotTable.

Step 1 — Add the three remaining measures

• Open the workbook.
• Go to Power Pivot → Manage → Data View → Sales tab.
• In the Calculation Area, add the following three measures exactly as written:

Distinct Customers := DISTINCTCOUNT(Sales[CustomerID])

Average Order Value := DIVIDE([Total Sales], [Transaction Count])

Total Quantity := SUM(Sales[Quantity])

• Press Enter after each one.
• All three should appear in the Calculation Area with a calculated value showing below the measure name.

When you are done, your Calculation Area should contain exactly five measures:

• Total Sales.
• Transaction Count.
• Distinct Customers.
• Average Order Value.
• Total Quantity.

Step 2 — Validate in a PivotTable

• Return to Excel. Insert a PivotTable from This Workbook's Data Model.

Build the following layout:

• Rows: MonthName from the Dates table.
• Values: all five measures )Total Sales, Transaction Count, Distinct Customers, Average Order Value, and Total Quantity, in that order.
• Slicer: Category from the Products table.

Once built, answer the following questions by reading the PivotTable.

1. What is the Total Sales grand total across all months and categories?
2. Which month has the highest Average Order Value?
3. When you filter the slicer to Bikes, does the Distinct Customers count change?
4. What does that tell you about customer behaviour for that category?
5. When you switch the slicer from Bikes to Accessories, does Total Quantity go up or down?
6. What does that suggest about the difference between the two categories?