he CALCULATE() function is one of the most important functions in Data Analysis Expressions (DAX). It allows you to evaluate an expression under a modified filter context, making it essential for building interactive Power BI reports and business dashboards.

In this tutorial, we will learn the DAX CALCULATE() function with practical examples using the AdventureWorks sample dataset. We will cover basic calculations, multiple filters, filter removal, percentage of total, and year-over-year sales analysis.

Table of Contents

1. What Is the CALCULATE Function in DAX?
2. CALCULATE Syntax
3. Prepare the AdventureWorks Dataset
4. Example 1: Calculate Total Sales
5. Example 2: Calculate Sales for Blue Products
6. Example 3: Apply Multiple Filters
7. Example 4: Calculate Sales for a Specific Year
8. Example 5: Remove Filters Using REMOVEFILTERS
9. Example 6: Calculate Percentage of Total Sales
10. Example 7: Use KEEPFILTERS
11. Example 8: Use FILTER with CALCULATE
12. Example 9: Calculate Previous-Year Sales

1. What Is the CALCULATE Function in DAX?

The CALCULATE() function evaluates an expression in a modified filter context.

In Power BI, filter context determines which rows of data are included in a calculation. Filters can come from report slicers, visual selections, page filters, relationships, or DAX formulas.

For example, suppose you have a sales report containing all products. You want to calculate sales only for blue products without changing the original Total Sales measure.

You can use CALCULATE() to apply the required filter.

2. CALCULATE Syntax

The general syntax is:

CALCULATE(
    <Expression>,
    <Filter1>,
    <Filter2>
)

Parameters:

• Expression: The measure or calculation to evaluate.
• Filter1: An optional filter condition.
• Filter2: An additional filter condition.

You can specify multiple filter arguments. They may be Boolean filter expressions, table expressions, or filter-modifier functions.

For example:

Blue Sales =
CALCULATE(
    [Total Sales],
    Product[Color] = "Blue"
)

This formula evaluates the Total Sales measure with a filter for blue products.

Important: By default, a filter supplied to CALCULATE() can replace an existing filter on the same column. Functions such as KEEPFILTERS() can change this behavior.

Advanced CALCULATE functions to mention

These are useful for a follow-up video or an advanced section if you want a longer tutorial.

Example 1: Calculate Total Sales

First, create a basic measure to calculate total sales.

Total Sales =
SUM(Sales[Sales Amount])

Explanation:

The SUM() function adds the values in the Sales Amount column. Because this is a measure, Power BI recalculates it according to the current filter context.

For example, selecting a product category in a report can change the Total Sales value to reflect that category.

This measure will be reused in the remaining examples.

Example 2: Calculate Sales for Blue Products

Suppose you want to calculate sales only for products whose color is blue.

Blue Sales =
CALCULATE(
    [Total Sales],
    Product[Color] = "Blue"
)

Explanation:

• [Total Sales] is the expression being evaluated.
• Product[Color] = "Blue" applies a filter to the Product table.
• The measure returns sales associated with blue products when the model relationships allow the product filter to reach the sales table.

This is one of the simplest and most useful examples of CALCULATE().

Example 3: Apply Multiple Filters

You can apply multiple conditions in the same CALCULATE() function.

Blue Bikes Sales =
CALCULATE(
    [Total Sales],
    Product[Color] = "Blue",
    Product[Category] = "Bikes"
)

Example 4: Calculate Sales for a Specific Year

Suppose your report contains several years of sales, but you want a measure that calculates sales for 2020.

Sales in 2020 =
CALCULATE(
    [Total Sales],
    'Date'[Year] = 2020
)

Example 5: Remove Filters Using REMOVEFILTERS

Sometimes you need to calculate sales across all products, regardless of the selected product or category.

Sales All Products =
CALCULATE(
    [Total Sales],
    REMOVEFILTERS(Product)
)

The REMOVEFILTERS() function removes filters from the Product table for this calculation.

Other filters, such as the selected year or region, can remain active.

This is useful when comparing the sales of an individual category with sales across all products.

For example, a report user might select Bikes in a slicer. The Total Sales measure could then show sales for Bikes, while Sales All Products calculates sales across the full product range, subject to other active filters.

Example 6: Calculate Percentage of Total Sales

Percentage-of-total calculations are common in Power BI dashboards.

Sales % of All Products =
DIVIDE(
    [Total Sales],
    CALCULATE(
        [Total Sales],
        REMOVEFILTERS(Product)
    )
)

Example 7: Use KEEPFILTERS

By default, CALCULATE() can replace an existing filter on the same column.

KEEPFILTERS() changes the behavior so the new filter is intersected with the existing filter.

Blue Sales Keep Filters =
CALCULATE(
    [Total Sales],
    KEEPFILTERS(
        Product[Color] = "Blue"
    )
)

Use FILTER with CALCULATE

For more complex filtering requirements, you can use the FILTER() function.

High Value Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        Sales,
        Sales[Sales Amount] > 1000
    )
)

Example 9: Calculate Previous-Year Sales

Time-intelligence calculations are another common application of CALCULATE().

First, ensure your model has a proper Date table with a continuous date column, a valid relationship to the sales date, and appropriate date-table configuration.

Create the following measure:

Previous Year Sales =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR('Date'[Date])
)

This measure evaluates sales for the corresponding period in the previous year.

Next, calculate the difference between current sales and previous-year sales.

Sales YoY Growth =
[Total Sales] - [Previous Year Sales]

Finally, calculate the percentage change.

Sales YoY Growth % =
DIVIDE(
    [Sales YoY Growth],
    [Previous Year Sales]
)

Format Sales YoY Growth % as a percentage.

Explanation:

• SAMEPERIODLASTYEAR() identifies the corresponding date period from the previous year.
• CALCULATE() evaluates Total Sales using that previous-year period.
• The growth measure calculates the difference between the current and previous periods.
• The percentage measure divides that difference by previous-year sales.

These measures are useful for comparing business performance across months, quarters, and years. Results depend on the selected date context and the availability of historical data.