The sum of the sales. Name the measure "Revenue".
Lesson theory
The chain's manager wants a report that can be sliced in a PivotTable by product, store and month. Build the set of measures from scratch — the model has no measures yet.
- Revenue, cost, profit and margin;
- the category's share of the revenue of all products;
- growth over the previous year;
- average revenue per store:
AVERAGEX(VALUES(Stores[Store]), [Revenue])— an iterator over the list of stores, evaluating the measure with each store's own filter; - the revenue of the best month:
MAXX(VALUES(Calendar[Period]), [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).
Practice is part of Pro
The live spreadsheet, task checking and .xlsx files in all 170 lessons. Your progress is saved in this browser; sync across devices is coming soon.