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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month, and much more.
O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.
Julian F.
Head of Cybersecurity
I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.
Addison B.
Field Engineer
I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.
Amir M.
Data Platform Tech Lead
I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.
O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.
