One of the most challenging and useful DAX functions to master in building complex measures and calculated columns is the CALCULATE function. In what follows I’ll describe two specific scenarios where knowing how and when to use CALCULATE is a must. My hope is that you will understand these examples and be able to confidently extend them to more challenging situations.
To set the table for how and when to use CALCULATE, let’s do a quick review of what is meant by “filter context” in Power BI.
Simply put, filter context is the selection of rows from various tables in your data model that are available for use in measures that aggregate data, e.g. SUM, AVERAGE, COUNT. This selection can be done by many elements of a Power BI Report, such as:
- Report Filter
- Page Filter
- Visual Filter
Additionally, filter context is expressed whenever the values in a column are explicitly shown in a visualization, thereby changing how measures will display their value “for each unique value in the column” instead of “for all the values in the column”.
For example, there is no filter context on a table showing only the “Sum of Sale Amount” from all records in a Sales table:
However, there is filter context when you add the column for “Month” to this table, and inspect the values for “Sum of Sale Amount” now “filtered” by the unique values from the “Month” column:
So the “current filter context” describes the interaction of all these elements concurrently.
Finally, keep in mind that an explicit filter on one table may also be filtering records in a related table with the appropriate directionality on that relationship, too.
CALCULATE Modifies Filter Context
The CALCULATE function takes two types of arguments:
You can have several filter arguments to a single CALCULATE statement.
- Using a function like FILTER as a filter argument, you can *add* a new filter on a column not currently filtered in the current filter context.
- Using a command like ALL as a filter argument, you can *remove* a specific filter on 1 column, multiple columns, or an entire table in the current filter context.
When CALCULATE is evaluated, the filter arguments are evaluated first and the current filter context is modified. Then the expression argument is evaluated and a value returned.
Let’s look at a simple example:
You have Power BI Desktop file with a table of Sales transaction data. Each Sales record contains a Sale Date, a Sale Amount and a Product. You also have a Product table having a one-to-many relationship with Sales.
‘Product’[Product Name] has a 1:M relationship with ‘Sales’[Product].
You place a Table visual on the page, showing the sum of Sale Amount for each Product found in the Sales Data, i.e. “Sales by Product”. Specifically, you add the ‘Sales’[Product] and ‘Sales’[Sale Amount] columns:
There are no Report Filters, Page Filters, or Visual Filters.
You also want to show Total Sales for all Products, and the percentage of Total Sales that is contributed by the sales from each Product.
- How would you show “Total Sales” in this table?
- How will you calculate “Percent of Total Sales” without “Total Sales”?
For a Measure: “Total Sales” with CALCULATE
The trick here is that you need to ignore the current filter context for the ‘Sales’[Product] column in order to calculate and display “Total Sales” for all Products in this table alongside “Sales by Product”. If you create a measures that does not ignore the current filter context for the ‘Sales’[Product] column, you will just see the sum of Sale Amount for each Product, not all Products. And you already have the sum of each Product in the table. You use the CALCULATE function to get this done correctly.
Total Sales := CALCULATE (SUM(‘Sales’[Sale Amount]),ALL(‘Sales’[Product]))
The first argument to CALCULATE is the expression “SUM(‘Sales’[Sale Amount])”
The second (and only) argument to CALCULATE is the filter “ALL(‘Sales’[Product])”.
Before the first argument is evaluated and the result returned for the measure [Total Sales], the current filter context is modified to remove any current filters on the ‘Sales’[Product] column. This change in filter context means that you will calculate the value for SUM(‘Sales’[Sale Amount]) using “ALL” Products…not *each* Product as defined by the current filter context. So no matter what other filters you may have on the ‘Sales’[Product] column, you will always see the same value for this [Total Sales] measure in this table, i.e. the sum of all records in the ‘Sales’[Sale Amount] column where no filter exists on the ‘Sales’[Product] column.
Finally, to get your value for “Percent of Total Sales”, you’ll divide Total Sales and Sales by Product:
Percent of Total Sales := DIVIDE ([Total Sales], SUM(‘Sales’[Sale Amount}))
For a Calculated Column: Sum of Sales by Product
The other scenario where CALCULATE comes in super handy is if you wanted to have a current total of “Sales Amount by Product” stored as a value in a calculated column in the Product table. Whenever you do a data refresh, this value would be updated with the sum of [Sale Amount] all related Sales records.
Let’s say you create a new calculated column in the Product table called “Product Sales”, with the following DAX expression:
‘Product’[Product Sales]=SUM(‘Sales’[Sale Amount])
If you then display the Sum of Product Sales by Product Name in a Table visual, the value for Sum of Product Sales you see for each Product Name is the sum of *all* Sale Amount in the Sales table, and the grand Total is 6*334 = 2004. Specifically, you add the ‘Product’[Product Name] and ‘Product’[Product Sales] columns to your table, using a SUM on [Product Sales]:
But that’s not what you want, and it’s just plain wrong. For each Product Name in the Product table with related Sales data, you only want the sum of all Sales for each Product.
The problem here is that there is no filter context associated with an expression for a calculated column…UNLESS you use a CALCULATE function to wrap your expression, turning “row context” into “filter context”.
‘Product’[Product Sales]=CALCULATE(SUM(‘Sales’[Sale Amount]))
Notice the absence of any filter arguments in the CALCULATE statement. CALCULATE used in this way requires no explicit filter argument(s). CALCULATE will inspect the value for ‘Product’[Product Name] on each row of the Product table (i.e. the “row context”, or current row), and effectively push that value as a filter to the Sales table before evaluating the expression SUM(‘Sales’[Sale Amount]) for the value of ‘Product’[Product Sales] in that row, using only the related records from the Sales table for the calculation.
Now when you inspect the values for ‘Product’[Product Sales], you’ll see that each row has only the sum of related records in the Sales table:
There are many other scenarios and ways to use CALCULATE, but these two scenarios will hopefully help you apply CALCULATE in different and more complex ways as you develop your own Power BI data models, report and dashboards.