Power BI Calculate

What is Power BI Calculate Function?

Power BI Calculate is a powerful function used to manipulate data and create complex calculations in a data model. It evaluates a table expression and returns a single scalar value. It can override filters, create new columns, and modify existing columns. The Power BI Calculate function can be used with other DAX functions to create complex formulas.

How To Use Dax Calculate Function in Power BI?

Syntax

The syntax for the DAX Calculate function in Power BI is:

• Expression is the condition to be evaluated, followed by filters. These filters can be Boolean or table filter expressions or filter modification functions. Let’s quickly review these filters to understand the concept in detail.Boolean Filter Expression
  • This filter evaluates an expression as TRUE or FALSE.
  • It can reference columns from a single table but can’t reference measures or use a nested CALCULATE function.
• Table Filter Expression
  • This filter evaluates a table object as a filter and returns a table object.
  • It allows users to apply complex filter conditions through the use of the FILTER function.
• Filter Modification Functions
  • This function allows users to provide additional control when modifying the filter context.
  • It allows multiple filter contexts, such as
    • Add filter without removing existing filters.
    • Remove all or customized filters such as specific columns or all columns of a single table.
    • Modify filter direction or disable relationships.

To use the calculate function in Power BI, you can follow these steps:

Step 1: Open Power BI Desktop, Import a dataset into Power BI using Data Connection

Step 2: Click on the New Measure option in the Modeling tab or Right-click on the table to select the New Measure option.

Step 3: Enter the DAX formula in the formula bar using the Calculate function and click on the Commit icon to create the measure.

Alternatively, you can use the Quick Measure option by right-clicking on the table and creating a measure to calculate a value with a filter applied. It will auto-create a DAX expression to calculate a value.

You can use aggregated functions within CALCULATE function, provided it returns a scalar value.

For example, Creating a measure of Total Sales for products with the highest discounts

You can perform several data manipulation and calculations such as Power BI calculate column, Power BI calculate sum, Power BI calculate table, Power BI calculate the average, and Power BI calculate with a filter using CALCULATE function. Let’s go through a few examples below to understand this function and its context of implementation.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

Example #1 – Calculation of Total Sales in Q1 across all products

In this example, we will calculate the total sales in Q1, i.e. January, February, and March months combined across all products using the financials dataset with the Power BI calculate Sum function.

Step 1: Import the dataset in Power BI using Data Connection.

Step 2: Create a new measure either by choosing the New Measure when you right-click on the financials table or the New Measure option in the Modeling tab

Step 3: Rename the measure, enter the DAX formula to calculate the total sales with filters on the month name to restrict it to January, February, and March months, and then click on the Commit icon.

It will create a new measure TotalSales in Q1 in the financials table.

Step 4: Drag this attribute to Canvas and select chart type as Stacked Column Chart to create a report of Total Sales in Q1 across all the products.

Example #2 – Calculation of Total Revenue for a particular segment, and year

Let’s say you want to calculate the total revenue for a particular segment and year across all the months to understand the trend. You can achieve this by using Power BI Calculate with filter function as highlighted below:

Step 1: Create a new column Total Revenue either by choosing the New Column when you right-click on the financials table or the New Column option in the Modeling tab

Step 2: Rename the column to Total Revenue, enter the DAX formula to calculate the Total Revenue, and click on the Commit icon.

It will create the new column Total Revenue in the financials table

Step 3: Create a new measure of Total Revenue for a particular segment and year on the financials table either by choosing the New Measure when you right-click on the financials table or the New Measure option in the Modeling tab

Step 4: Rename the measure, enter the DAX formula to calculate the Total Revenue for Government and Enterprise segment and year, and then click on the Commit icon.

This will create a new measure of Total Revenue by segment by year in the financials table

Step 5: Drag the measure to canvas and select the chart-type Card to create a report of the Total Revenue of combined segments, i.e., Government and Enterprise, for 2014.

Example #3 – Calculate the Average Discount offered per product category

In this example, we will calculate the average discount offered per product category using the Power BI Calculate average function.

Step 1: Create a new measure The Average Discount offered per product category on the financials table. You can either choose the New Measure option when you right-click on the financials table or select the New Measure option by navigating to the Modeling tab.

Step 2: Define the DAX formula by entering the expression in the formula tab and clicking on the Commit icon.

This will create a new measure in the financials table.

Step 3: Drag the measure onto the canvas along with the product field and select the chart type as Donut chart.

Important Things to Note

• In the Power BI CALCULATE function, the order of the filter arguments matters.
• You can use multiple filters by using logical operators in excel such as AND(&&) or OR(||) to define an expression.
• It can override other filters applied to the table or column being filtered.
•  Power BI Calculate can be used to create dynamic measures and columns based on user input.
• If you are using DirectQuery mode for calculated columns or even row-level security rules, this function can’t be used

Frequently Asked Questions (FAQs)

Recommended Articles

Guide to Power BI Calculate. Here we learn how to use calculate function in Power BI (tables/columns) with step by step examples. You can learn more from the following articles –

• Power BI Average
• Power BI Features
• Power BI Share Reports