2. First Measures: SUM, COUNTROWS, DIVIDE

A measure is a formula that is calculated not for a row but for each cell of a PivotTable: the "Beverages" cell sees only beverage sales, and the "Grand Total" sees everything. The filter comes from the lookup table through the relationship: the category lives in "Products", and the amounts live in "Sales".

• SUM(Sales[Amount]) — the sum of a column in the current filter. A column is always written with its table: Table[Column];
• COUNTROWS(Sales) — how many sales rows are visible;
• DISTINCTCOUNT(Sales[Product]) — how many different products;
• DIVIDE(a, b) — division that returns blank instead of an error when b = 0;
• another measure is referenced by its name in brackets: [Revenue].

In the trainer the model is already built: the Sales fact table on the first sheet, and the Products, Stores and Calendar lookup tables on the "Lookups" sheet. The relationships are shown in the Power Pivot panel under the sheet (expand "Data model"). The "+ Measure" button is there too: the name goes on the left, the formula on the right, and Enter saves it. Below is a PivotTable of your measures; change its rows with the "PivotTable rows" list.

The argument separator is a comma, as in English Excel (a semicolon also works if you use one throughout the formula).