All about the Power BI Calculate Function Simplified 101

Visual Analytics is the union of Data Analytics and Visualisations. This problem-solving approach is concerned with effectively facilitating high-level, complex activities such as reasoning and data-driven decision-making by integrating interactive visual representations with underlying analytical processes. Power BI is a Business Intelligence (BI) tool and a Visual Analytics engine offered by Microsoft that provides you with real-time high-level analytics, extensive modeling, and custom development. It has led the charge in making business analysis more efficient through services that are intuitive, interactive, and easy to use.

In this article, you will gain information about Power BI Calculate Function. You will also gain a holistic understanding of Power BI, its key features, DAX functions in Power BI, and the usage of Power BI Calculate Function. Read along to find out in-depth information about Power BI CALCULATE Function.

What is Power BI?

Power BI is a part of Microsoft Power Platform and a proprietary Data Visualization and Business Intelligence platform. It is one of the widely used tools among organizations to analyze business data and generate reports. Power BI uses a collection of various in-built software services, apps, and connectors to deeply integrate with data to provide immersive visuals, interactive reports, and generate insights.

Key Features of Power BI

Some of the main features of Power BI are listed below.

• Hybrid Development: Power BI offers integrations with many connectors that allow users to connect to various data sources.
• Modeling View: Power BI allows users to divide and slice complex data models into a simpler form, separate diagrams, multi-select objects. 
• AI Support: Power BI supports Artificial Intelligence in Data Analytics that users can leverage to prepare data, build Machine Learning models and quickly identify insights from structures as was unstructured data. 
• Quick Insights: Power BI makes it easier for users to create subsets of data and automatically Data Analytics to that information.
• Report Sharing: Users can easily share their reports with other users of the organization without worrying about data security. 

What are DAX Functions in Power BI?

Data Analysis Expressions (DAX) are a set of functions and operators that can be used to build formulas and expressions in Microsoft SQL Server Analysis Services, Excel Power Pivot, and Power BI Desktop. A DAX function is a predefined formula that performs calculations on the values passed to it as arguments.

Some of the Power BI DAX functions are:

• Time Intelligence Functions: These functions assist you in creating computations that make use of the built-in calendar and date knowledge. You can make meaningful sales, inventory, and other comparisons over similar time periods by combining time and date ranges with aggregations or other calculations.
• Date & Time Functions: These are used to do calculations on date and time values.
• Logical Functions: These are used to logically assess an expression or argument and return TRUE or FALSE depending on whether or not the condition is fulfilled.
• Mathematical & Trigonometric Functions: These are used to conduct a variety of mathematical operations on the values specified.

Key Points about DAX Functions

Here are some unique facts about DAX functions that you must know in order to understand them better:

• Any DAX function will always refer to an entire column/field or table. It will never make any reference to personal values. If you want to use the functions on different values in a column, you must use filters in a DAX formula.
• The flexibility of DAX functions allows you to create a formula that is applied row by row. Calculations or formulas are applied in accordance with the context of the values in each row.
• In some cases, DAX functions return an entire table that can be used in other DAX formulas that require a complete set of values. However, you are unable to view the contents of this table.
• Time intelligence functions are a subset of DAX functions. These functions are used to compute time/date ranges and periods.

What is Power BI Calculate Function?

One of the most dynamic functions that can help you add deeper insights to your reports is the CALCULATE function. The CALCULATE function allows you to change the context of a calculation within a measure, where the context is determined by the environment in which your calculation is performed.

Using the CALCULATE function, you can easily branch out to time intelligence calculations. You’ll be able to generate high-quality insights within Power BI by using Power BI Calculate Functions.

For diving deeper into Power BI CALCULATE Function, you can go through the following:

• Basic Syntax
• Examples
• Remarks

1) Basic Syntax

The DAX syntax for the Power BI CALCULATE function is as follows:

CALCULATE(<[expression]>,([filter1],([filter2],([filter...]))))

The syntax of the Power BI CALCULATE function can be broken down into two parts:

• The Aggregation: The first part of the expression is the aggregation piece. This is where you can place whatever aggregation function you’d like whether it’s SUM(Sales), AVG(Price), or something else.
• The Filter: The second portion is your filter criteria. This part will define the data set that you are applying the aggregation in the first part to. 

Filters can be any of the following:

• Boolean filter expressions
• Table filter expressions
• Filter modification functions

2) Examples

Different examples and use cases of Power BI CALCULATE Function are illustrated below:

• Single Filter Criteria
• Multiple Filter Criteria
• Using FILTER Function
• Using ALL Function
• Using ALLEXCEPT Function

A) Single Filter Criteria

In this example, you want to create a measure that will always show you the sum of Sales for the East region in your Sales dataset. Here, you already know that you need to calculate the sum of your Sales field but also you need to specify the sum of your Sales field when the “Region = East“.

So, in this case, SUM(Order[Sales]) is the expression, and [Region]=”East” is the only filter criteria.

// East Region Sales = 

CALCULATE(
    SUM(Orders[Sales]),
    [Region]="East")
    )

Another common use case of using one filter criteria is to find only Sales from a specific year, often the max year (that specific year).

In the following example, you are being given Sales data at the year level, so to calculate the sum of overall Sales for the most recent year i.e, the max_year, you can go through the following code of power BI Calculate Function. 

So, in this case of Power BI Calculate Function, SUM(Order[Sales]) is the expression, and Orders[Year]=max_year is the only filter criteria.

//Sales CY = 

VAR max_year = MAX(Orders[Year])

RETURN

CALCULATE(
    SUM(Orders[Sales]),
   Orders[Year]=max_year
)

B) Multiple Filter Criteria

Many a time, it will be required to apply more than one criteria to the dataset that is being used for performing calculations using the POWER BI CALCULATE Function.

In the following example, you are calculating the most recent revenues using a specific NCAA conference from an NCAA financials dataset. Here, multiple criteria have been applied for this calculation. 

So, in this case of Power BI Calculate Function, SUM(NCAA_Financials_FCT[Total Revenues]) is the expression, and NCAA_Financials_FCT[Year]=max_year and ‘Conference DIM'[Conf_Name]=”Big Ten Conference”are the two filter criteria. In this case, SUM will be calculated only when both criteria are fulfilled i.e, the AND criteria.

// Big 10 Revenues CY = 

VAR max_year = MAX(NCAA_Financials_FCT[Year])

RETURN

CALCULATE(
    SUM(NCAA_Financials_FCT[Total Revenues]),
    NCAA_Financials_FCT[Year]=max_year,
    'Conference DIM'[Conf_Name]="Big Ten Conference"
)

When applying multiple filters for an OR criteria you need to use a “||” to add on criteria.

So, in this case of Power BI Calculate Function, SUM(NCAA_Financials_FCT[Total Revenues]) is the expression, and NCAA_Financials_FCT[Year]=max_year and NCAA_Financials_FCT[CONF_ID]=”1″ are the two filter criteria. In this case, SUM will be calculated when either one of the two criteria is fulfilled i.e, the OR criteria.

The following Power BI Calculate Function will return all data that is either in the max_year or for [CONF ID] = 1.

//Big 10 Revenues CY Multi = 

VAR max_year = MAX(NCAA_Financials_FCT[Year])

RETURN

CALCULATE(
    SUM(NCAA_Financials_FCT[Total Revenues]),
    NCAA_Financials_FCT[Year]=max_year 
    || NCAA_Financials_FCT[CONF_ID]="1"
    )

C) Using FILTER Function

The following example uses the FILTER Function to apply complex filter conditions along with the Power BI CALCULATE Function.

Red Sales :=
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( 'Product'[Color] ),
        'Product'[Color] = "Red"
    )
)

In this case, ALL is a filter modifier function. For further information on filter modifier functions, visit here.

D) Using ALL Function

The ALL function will compute the expression part of your CALCULATE function on all of the data in the data set, irrespective of any filters that have been applied. Essentially, the ALL function overrides any filters that are present.

So, in this case of Power BI Calculate Function, SUM(NCAA_Financials_FCT[Total Revenues]) is the expression, and ALL FUNCTION instructs to read all the data in NCAA_Financials_FCT.

//Revenues ALL = 

CALCULATE(
    SUM(NCAA_Financials_FCT[Total Revenues]),
    ALL(NCAA_Financials_FCT)
)

The above code will result in the sum of Total Revenues being displayed irrespective of any filters.

In the image given below, it can be seen that even when a Conference field name is being displayed, the Total Revenues of the overall data are getting displayed.

You can include some filter criteria that will always stay applied no matter what other filters are applied to the measure. As in the following example, it has specified a certain year i.e., 2017 to calculate Revenues. Irrespective of other years or conferences chosen, this value will remain constant. 

//Revenues ALL 2017= 

CALCULATE(
    SUM(NCAA_Financials_FCT[Total Revenues]),
    FILTER(ALL(NCAA_Financials_FCT),
        NCAA_Financials_FCT[Year]=2017)
)

E) Using ALLEXCEPT Function

The ALLEXCEPT function returns all of your data in the same way that the ALL function does, EXCEPT it takes into account some filter fields that you specify.

As given in the following example, if you want the Total Revenues to be seen according to the conference, but you want the Years to be filtered. Then, in the given code it can be seen that the Year specified will be the only field that can filter the dataset that is being aggregated. 

//Revenues ALLEXCEPT Year =

CALCULATE (
    SUM ( NCAA_Financials_FCT[Total Revenues] ),
    ALLEXCEPT ( NCAA_Financials_FCT,
        NCAA_Financials_FCT[Year] )
)

When the field is added to the table, it is clear that the year is now being considered for the ALLEXCEPT Year measure.

3) Remarks

• When filter expressions are passed to the Power BI CALCULATE function, the filter context is modified to evaluate the expression. When the filter expression is not wrapped in the KEEPFILTERS function, there are two possible standard outcomes:
  • If the columns (or tables) are not in the filter context, new filters will be added to evaluate the Power BI Calculate Function expression.
  • If the columns (or tables) are already in the filter context, the new filters will overwrite the existing filters in order to evaluate the Power BI CALCULATE function expression.
• When used without filters, the Power BI CALCULATE function meets a specific requirement. It changes the row context to the filter context. When an expression (not a model measure) that summarises model data needs to be evaluated in a row context then it is required. This scenario can occur in a calculated column formula or when evaluating an expression in an iterator function. It should be noted that when a model measure is used in a row context, context transition occurs automatically.
• When used in calculated columns or row-level security (RLS) rules, the Power BI Calculate function is not supported in DirectQuery mode.

Conclusion

In this article, you have learned about Power BI Calculate Function. This article also provided information on Power BI, its key features, DAX functions in Power BI, and the usage of Power BI Calculate Function.

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations with a few clicks.

Want to give Hevo a try? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing price, which will assist you in selecting the best plan for your requirements.

Share your experience of understanding the Power BI CALCULATE Function in the comment section below! We would love to hear your thoughts.