From Data Model to Reporting

The model defines what can be combined. If a relationship path exists between two tables, any pivot table can combine fields from both — no formulas needed. If no path exists, the connection cannot be made.

The Three Business Questions

1. Which region is driving the most revenue?

• Source table: Customers;
• Rows: Region;
• Columns: Category (Products);
• Values:Total Sales (measure).

2. How are sales trending month by month?

• Source table: Dates;
• Rows: Year, then Month Name;
• Values: Total Sales (measure).

3. How do customer segments compare?

• Source table: Customers;
• Rows: Segment;
• Values: Total Sales, Transaction Count, Average Order Value.

Formatting Pivot Table Values

Raw numbers in a pivot table are harder to read than formatted ones — especially when sharing with stakeholders. Apply currency formatting to any monetary measure directly inside the pivot table:

1. Click any cell in the measure column you want to format;
2. Go to PivotTable Analyze → Field Settings;
3. Click Number Format at the bottom of the dialog;
4. Select Currency, choose the appropriate symbol, and click OK.

Sorting Month Names Correctly

Month names are text values. Excel sorts text alphabetically by default — which places April before January and February before March. In any time-based pivot table, this must be corrected before the data is meaningful.

1. Right-click any month name in the pivot table rows area
2. Select Sort → More Sort Options;
3. Choose Ascending to sort January → December;
4. For full control over order, use the Manual option to drag months into the correct sequence.

The Model as the Reporting Engine

Each of the three pivot tables draws from different tables in the model simultaneously. Pivot table 1 combines Customers, Products, and Sales in a single view.

Before data modeling, combining Region, Category, and Sales totals in one table required VLOOKUP or SUMIFS formulas that had to be rewritten every time the data changed. With the model in place, the same result is produced by dragging three fields into a pivot table — and it updates automatically when new data is loaded.

Task

Your job is to build three PivotTables, each one answering a specific business question. Each PivotTable must draw fields from at least two different tables. Create each PivotTable on a new sheet and name the sheet as indicated.

PivotTable 1 — Revenue by Segment and Category (sheet name: PT_Task1)

Business question: Which customer segment generates the most revenue, and does the category breakdown differ across segments?

Insert a model-based PivotTable (Insert → PivotTable → Use this workbook's Data Model) on a new sheet named PT_Task1, then:

• Add Segment from the Customers table to Rows.
• Add Category from the Products table to Columns.
• Add the [Total Sales] measure from the Sales table to Values.
• Format the values as currency with two decimal places.

PivotTable 2 — Monthly Transaction Count (sheet name: PT_Task2)

Business question: How many orders were placed each month, and which quarter had the highest volume?

Insert a second model-based PivotTable on a new sheet named PT_Task2, then:

• Add Quarter from the Dates table to Rows.
• Add MonthName from the Dates table to Rows, nested below Quarter.
• Add the [Transaction Count] measure from the Sales table to Values.
• Format the values as whole numbers (no decimal places).

Verify: the Quarter subtotals should equal the sum of the monthly Transaction Count values within that quarter. If they do not, check that your rows are nested correctly (Quarter outer, MonthName inner).

PivotTable 3 — Three Measures by Region (sheet name: PT_Task3)

Business question: How do the four regions compare on total revenue, number of orders, and average order size?

Insert a third model-based PivotTable on a new sheet named PT_Task3, then:

• Add Region from the Customers table to Rows.
• Add [Total Sales], [Transaction Count], and [Avg Order Value] from the Sales table to Values.
• Format Total Sales and Avg Order Value as currency. Format Transaction Count as a whole number.