From Excel Formulas to DAX

Every DAX measure has exactly three parts written in a specific order:

DAX Function Name := Expression

Total Sales := SUM(Sales[Total])

Measures are written in the Calculation Area — the blank rows at the bottom of each table in Power Pivot's grid view. Any blank cell in this area can hold a measure. To keep the model organised, it is good practice to write measures below the table they primarily reference — Total Sales below the Sales table, for example.

1. Open Power Pivot → Manage and navigate to grid view;
2. Select the table the measure relates to (e.g. Sales);
3. Click any blank cell in the Calculation Area below the table data;
4. Type the measure name, := , and the expression — text appears in the formula bar at the top, not directly in the cell;
5. Press Enter to confirm — the result appears in the calculation area cell.

Once a measure is saved, it immediately appears in the pivot table fields pane alongside the regular columns of its table. Measures are identified by a small fx icon next to their name — this distinguishes them from regular data columns. Dragging or clicking a measure into the Values area adds it to the pivot table, and it responds instantly to any slicer or filter that is active.

Task

Step 1 — Write your first two measures

• Open the same Workbook you've been using from 3.1 through 3.4 (not the 3.5 workbook).
• Open Power Pivot → Manage and switch to Data View.
• Click the Sales tab at the bottom of the Power Pivot window.

In the Calculation Area below the data, create the following two measures exactly as written:

Total Sales := SUM(Sales[Total])

Transaction Count := COUNTROWS(Sales)

Press Enter after each one. Both should appear in the Calculation Area showing a calculated result.

Then return to Excel and do the following:

• Insert a PivotTable from This Workbook's Data Model.
• Place Region from the Customers table on the Rows.
• Place Total Sales and Transaction Count from the Sales table in the Values area.
• Insert a slicer on Category from the Products table.
• Click Bikes in the slicer and observe how both measures update.