Power BI Tips: Calculate | One of the most used DAX functions

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 filter context specified by a filter condition already exists, it will override the already-existing filter context with the new one that’s been specified in the CALCULATE expression.
• If the filter context does not exist, it will add new filter context according to the filter condition specified.

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:

1. 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.
2. 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 )