4. ALL and REMOVEFILTERS: Shares

To calculate a share, you need a denominator without the row's filter: "category revenue / revenue of all categories".

• ALL(Products) inside CALCULATE removes all filters from the "Products" table (category, product). Filters on city and year stay;
• ALL(Products[Category]) removes the filter from just one column;
• REMOVEFILTERS() with no arguments removes every filter in the model: the grand total for the whole chain;
• divide with DIVIDE and show the result as a percentage.

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).