How to Use the DAX CALCULATE Function in Excel

Read Time: 2 mins

Data Analysis Expression (DAX) language is integral in Power Pivot, Power BI, and Analysis Services in Microsoft Excel. These DAX formulas combine operators, functions, and values to execute complex calculations and data queries in tabular models.

CALCULATE is one of the most important DAX functions to simplify scenarios with custom data filters.

DAX CALCULATE Step-by-step

Imagine you’re the national sales manager for a laptop case manufacturer, and your workbook has tabular sales data broken down by month. You want more insight into how well premium cases sold last year.

1. Create Your New Measure

Click on an empty cell, navigate to “measures,” and select “new measure.” Name the calculation you want to establish, which might be “premium case sales” in this example. In the bottom portion of the dialogue box, type “calculate” to begin building the DAX query.

2. Choose Your Expression

This value represents the component you want to adjust using the filters you select. In other words, what is the data you’ll filter for context? For this example, the expression is “total sales” — the value you’ll filter to pull premium versus standard data in the next step. Add this expression after the opening bracket next to “CALCULATE” in the dialogue window. Typing the first few letters should automatically display a list of data tables to select.

3. Add Your Filter Context

Next, determine the filters based on the relationships between your tables. You can visualize these relationships in the “Power Pivot” option under “Data model,” then “manage,” and “diagram view.” Add the filter “case type” by typing in the first few letters and choosing this data table.

Now, you enter the filter context — the data you want to include in your query. There will be two options in this table — premium and standard. To calculate sales for premium cases, add an equal sign after the table name and then “premium” in quotes. That indicates the program should pull only data where the “case type” field contains the value “premium.”

Once your filters are in place, end the expression with a closing bracket and click “OK.” Your workbook now displays a new column for “premium case sales” month by month.

You can customize your DAX queries with more filters or additional measures. For example, you could add a new measure for “case color” or “case size” to further break down your premium case sales data.