In order to offer meaningful insights from data analysis work in Power BI, it is commonly necessary to perform complex calculations involving all kinds of metrics, sub-totaling functions, data filtering, and relationship 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, we must understand the concept of ‘filter context’. Filter context is one of the most important DAX concepts and it will be mentioned frequently during this walk-through. Filter context might be defined as the specific set of data that 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 at the CALCULATE function arguments:

As noticed, this function asks for only one mandatory argument: **Expression**.

Additionally, the optional arguments are **Filter1**, **Filter2**, **Filter3,**…, **Filter(n). **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*the already-existingwith the new one that’s been specified in the CALCULATE expression.*filter context* - If the
does not exist, it will add new filter context according to the filter condition specified.*filter context*

A few 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 is as follows:

**Example 1: Percentage of total**

This first example represents a very common scenario, where it is 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 number of sales:

Go to Home tab > Calculations group > New Measure

Then, write this DAX formula:

Sum Of Sales – SUM(FactInternetSales[SalesAmount])

To get the output as seen below; You can add a Matrix visual from the Report view. Then use EnglishProductSubcategoryName from DimProductSubcategory table in the *Rows* field and the defined **Sum Of Sales** measure in the *Values field.*

*(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 do we divide each item’s **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.

#### To do that, let’s define a new measure called Total Sales and define it as:

Total Sales = CALCULATE( [Sum Of Sales] , ALL( DimProductSubcategory ) )

To see what the output of this measure is, it is useful to include this new measure in the Values field of our *Matrix* visualization:

#### From this, we observe two important things:

- The measure shows the Total sales in each row, even with
**filter context**for each subcategory. This is because the**CALCULATE**function modified the existing**filter context**in each row by*overriding*it with its own:**ALL**( DimProductSubcategory ) function. For practical purposes, the**ALL**function overrides 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. Then, having effectively ignored the**filter context**of DimProductSubcategory, all subcategories are displayed in the Total Sales column.

#### Now that we have a usable **Total Sales** value for 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 the monthly sales data that starts in August 2005:

Let’s suppose that we want to know the number 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 bold is that 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 line’s 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:

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 how to use the DAX Calculate function in Power BI. For most posts view our blog page.

Until the post… ;) ( Visit : Data Bear )