In order to offer meaningful insights from data analysis work in Power BI, it is commonly necessary to perform complex calculations involving all kind of metrics, sub-totalling functions, data filtering and relationships handling.
For the experienced Power Pivot – Power BI user, the CALCULATE function of the DAX engine is by far one of the most useful tools that can simplify complex calculation scenarios with little effort. It is very similar to the Excel Sumif function, however, it does not have the same limitations thereof.
Before we take a look at the CALCULATE function, let’s look at the concept of the ‘filter context‘ because it will be mentioned frequently during the walk-through and is one of the most important concepts of DAX. A filter context might be defined as the specific set of data an expression is evaluated against, after all participating filters have been applied, such as rows, columns and slicers.
The Calculate Function in Power BI
Let’s start by taking a look on the CALCULATE function arguments:
As noticed, this function asks for only one mandatory argument: Expression. Additionally, the optional arguments are Filter1, Filter2, Filter3… and up to as many filter conditions as you might need.
So what does the CALCULATE function do exactly?
The CALCULATE function is useful because it can modify the filter context of the Expression inside, as indicated by the n number of filter conditions specified by the user, according to these steps:
- If the filter context specified by a filter condition already exists, it will override such already existing filter context with the new one specified in the CALCULATE expression,
- If the filter context does not exist at all, it will add a new one according to the filter condition specified.
A couple of examples can show the usage of this function. These will be based on data extracted from the widely known database AdventureWorks.
The model to be used as follows:
Example 1: Percentage out of a total
This first example represents a very common situation where it’s necessary to show the percentage that a value represents out of a total. Suppose we want to calculate the % Of Product Sales per Product Subcategory.
Using the FacInternetSales table, the initial step is to define a measure for the amount of sales:
Go to Home tab > Calculations group > New Measure
And write this DAX formula:
Sum Of Sales = SUM(FactInternetSales
In the Report view, a Matrix visualization using EnglishProductSubcategoryName from DimProductSubcategory table as Rows and the defined Sum Of Sales measure in Values will give the following output:
(For those who don’t still know, a Matrix visualization can be used as a sort of Power BI version of an Excel’s Pivot Table! This visualization is very useful for debugging while coding DAX)
The problem is, how to divide each Sum Of Sales by the Total shown? Our answer, tell DAX to ignore the filter context imposed by each subcategory row field using a combination of CALCULATE and ALL functions.
For that, let’s define a new measure:
Total Sales = CALCULATE( [Sum Of Sales] , ALL( DimProductSubcategory ) )
And then, include this new measure in the Values field of our Matrix visualization:
Now there are two things to observe:
- The measure always shows the Total sales in each row, though there is a filter context indicated by each subcategory. What happened is that the CALCULATE function modified the existing filter context in each row by overriding it with its own: ALL( DimProductSubcategory ). For practical purposes, this ALL function makes the measure ignore the existing filter context.
- Several new subcategory rows appeared with no values in the Sum Of Sales This is because those subcategories did not have any sales and thus they were not shown in the previous screenshot. Then, having effectively ignored the filter context of DimProductSubcategory, all subcategories are displayed in the Total Sales column.
And now that we have a usable Total Sales value in each row, we may define our desired % Of Total Sales measure:
% Of Total Sales = [Sum Of Sales] / [Total Sales]
Then, after including this measure in the Matrix visualization and removing the Total Sales measure from it we get:
There it is!
Let’s take a look at another example of the CALCULATE function.
Example 2: Sales Volume during working days
From the same AdventureWorks database, we have this monthly sales data starting at August 2005:
Let’s suppose that we want to know the amount of sales made during working days only, i.e. Monday to Friday. This example shows how the CALCULATE function can add completely new filter contexts to an expression.
First, we need a suitable Calendar table in our data model. The one used in this example has a DayName column which will be used for filtering the working days.
Now for our DAX expression:
Working Days Sales = CALCULATE([Sum Of Sales], DimCalendar[DayName] <> “Saturday”, DimCalendar[DayName] <> “Sunday”)
There are several ways to achieve this goal. For this example, we simply told the CALCULATE function to filter DayNames different from “Saturday” and different from “Sunday”. And yes! The reason why the “and” is bolded is because it’s a thing to consider when specifying multiple filtering conditions, as the CALCULATE function will perform a logical AND operation on all the filter conditions it has.
In case you need to specify multiple conditions and apply a logical OR operation on your conditions, you may use the vertical lines operator “||” for separating them. The only thing to remember is that within a filter condition, this OR operator cannot work on logical expressions involving more than one single column.
Back to the example, we may add the Working Days Sales measure to the Matrix visualization to show this:
And, using the calculate function, the goal has been achieved…
- Although superior, the calculate function is very similar to the Excel Sumif function.
- Understanding the filter context is essential. As we saw from our examples, the CALCULATE function modifies or adds filter contexts to a calculation.
- In our examples, two different types of filter conditions were used: a table returned by ALL( DimProductSubcategory ) and a logical filtering expression.
That wraps up our overview of the how to use the DAX Calculate function in Power BI.
Until the post… 😉 ( Visit : Data Bear )