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
specified by a filter condition already exists, it will*filter context**override*such already existingwith the new one specified in the CALCULATE expression,*filter context* - If the
does not exist at all, it will add a new one according to the filter condition specified.*filter context*

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…

**Summary**

- 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 )

## Leave A Comment