Modern Data Analytics in Excel

Chapter 9. Intermediate DAX for Power Pivot

In Chapter 8, you explored basic DAX measures for reporting. Now, in the final chapter of Part II, we dive into intermediate DAX tasks, enhancing PivotTable reporting in Excel.

To participate in the demonstrations, please open the ch_09.xlsx file, which is located in the ch_09 folder of the book’s companion repository. We will use the same retail sales dataset as in previous chapters.

This Excel workbook includes a PivotTable linked to the Data Model, and it features a predefined measure named Total sales. This measure calculates the sum of the Sales column from the orders table, and it will be used in various demonstrations that follow.

CALCULATE() and the Importance of Filter Context

In traditional PivotTables, all values adhere to the main filter. For instance, in Figure 9-1, if you filter by Ship Mode for “Express Air,” you can’t see total sales simultaneously. You either get overall sales or just Express Air sales, but not both.

Figure 9-1. Total sales is now evaluated in a filter context

In eloquent terms, every value in a PivotTable adheres to its “filter context.” However, the CALCULATE() function liberates measures from this constraint, enabling them to work within an modified filter context. This revolutionizes PivotTable capabilities.

For as powerful as the CALCULATE() function is, its syntax is rather simple, as shown in ...