The data model

Keep facts and descriptive attributes in suitable tables

An Orders table has one row per order line with Order, SKU, Sales and Profit. A Products table has one row per SKU with SKU and Category. Products describes the items; Orders records the events.

Instead of copying Category into every order row, a Data Model can relate Products[SKU] to Orders[SKU]. Products is the one side; Orders is the many side. Repeated SKUs in Orders are expected. Duplicate SKUs in the product lookup break the intended one-side key.

Relationships organize how tables work together; they do not automatically repair missing IDs, inconsistent types or the wrong data grain. Test the source keys and examine unmatched records.

Create a small model in supported Windows desktop Excel

1. Create Orders with O101/P101/100/20, O102/P102/900/90 and O103/P101/200/40, using headings Order, SKU, Sales, Profit.
2. Create Products with P101/Office and P102/Travel, using SKU and Category.
3. Convert each range to an Excel table and use meaningful table names Orders and Products.
4. Use Power Pivot → Add to Data Model, or a supported load-to-model method, for each table.
5. Open Power Pivot → Manage → Diagram View. Relate Products[SKU] to Orders[SKU]. Check one-to-many direction and matching Text key types.
6. Insert a PivotTable from the workbook Data Model. Put Products Category in Rows and a Sales measure in Values.

If the Power Pivot tab is available but disabled, it may need enabling as the Microsoft Power Pivot for Excel COM add-in. Availability depends on the Windows Excel edition/license.

Write measures rather than averaging row ratios

DAX is the model's formula language; it is different from worksheet formulas. Create these measures through Power Pivot's measure tools. In a measure dialog, enter the name separately and the formula beginning with =; other DAX displays show the name followed by :=.

SUM receives a model column to aggregate. DIVIDE receives numerator, denominator and an optional constant fallback; without a fallback it returns BLANK when division cannot be performed because the denominator is zero. Square-bracketed Total Profit and Total Sales refer to measures here, not worksheet addresses.

The sample has total Sales 1,200 and Profit 150, giving margin 12.5%. Office has Sales 300 and Profit 60, giving 20%. Travel has 900 and 90, giving 10%. The overall margin is a ratio of totals, not an average of the two category margins.

Measures evaluate in filter context

Put Category in a PivotTable row and Excel evaluates the measure under that category's filter. Select Office in a connected slicer and Total Sales becomes 300 because the Products-to-Orders relationship carries the selection to matching order lines.

A calculated column evaluates an expression for each row and stores results in the model. A measure evaluates an aggregation for the current report context. Use a measure for total margin so its numerator and denominator follow the selected scope.

If every category repeats the grand total, inspect the relationship and fields used in the report. If an unexpected blank category appears, inspect unmatched fact keys. A relationship can exist even when not every transaction key has a matching product.

Understand platform and checker limits

These authoring instructions require a supported Excel for Windows desktop edition with Data Model/Power Pivot capabilities. The full Power Pivot authoring add-in is not available in Excel for Mac or Excel for the web. Those platforms may open or interact with some existing workbooks, but do not assume equivalent model creation or refresh features.

The browser tasks below test modeling choices and measure interpretation. They do not execute DAX or create relationships. If your Excel does not provide the tools, learn the model concepts here and use ordinary tables and summaries for the practical alternative without buying software.