Create Calculated Measures in Power BI

In Microsoft Power BI, you can create a calculated measure (or numeric aggregated calculation) to define custom calculations from existing columns. This article shows you how to create calculated measures with an example.

A calculated measure in Power BI is a simple formula or DAX expression to create a new custom value based on existing data or other measure values. You can use them to perform complex calculations and develop KPIs (key performance indicators. For example, if your data doesn’t have the profit column, you can use Power BI calculated measure to generate from sales and product cost.

For simple arithmetic operations, you can manage without any knowledge of DAX. However, you should completely know how DAX works, its formulas and supporting functions (built-in) to unleash Power BI calculated measure full potential.

To demonstrate these Power BI calculated measures, we use the Data Source we created in Connect to SQL Server article.

Two Pie Charts Report 1

How to Create Calculated Measures in Power BI

This section will provide a step-by-step approach to creating calculated measures in Power BI. Although we use a simple DAX arithmetic operator in this example to create a calculated measure, the process will be the same for complex calculations. In order to create it, please navigate to the “Modeling” tab and click on the New Measure option.

Go to Modeling Tab to create Calculated Measures in Power BI 2

Clicking the Power BI New tab opens the DAX formula bar with the default measures name, as shown below. You can use built-in DAX functions and operators within the formula bar to write your custom calculations. So, start writing your DAX expression here.

Write DAX Expression 3

You can rename this as per your requirement. Let me rename this one as Total Sale.

Create Calculated Measures in Power BI 4

For the Power BI calculated measures demo purpose, we add the Internet Sales Amount, Reseller Sales Amount from Fact Internet Sales, and Fact Reseller Sales tables. Unlike calculated columns, you have to use the aggregated function to create them.

As you can see from the screenshot below, Power BI IntelliSense was showing the suggestions while I was typing.

Write a DAX Query 5

After writing the DAX expression, clicking enter or any key, a new Measure called TotalSale will create and validate the expression.

Create Calculated Measures in Power BI 6

While using the calculated measure, if you find that the desired output is wrong or needs a complex expression, you can modify the existing one using the formula bar.

Using Calculated Measures in Charts

Once the calculation works successfully, the next step is visualizing the newly created Calculated Measure. It acts as any standard measure, so drag and drop it into Visualization fields. This section covers how to use the Calculated Measures in Tables and Charts.

Let me add the calculated Measure we created earlier to a Power BI Table. Please refer to the Table Report article to understand the steps involved in creating a table.

Add to a Table 7

Let me minimize the Fields tab and Visualization so you can focus on table values.

Create Calculated Measures in Power BI 8

You can also apply filters on these too. From the screenshot below, you can see that we selected a few areas in our Pie Chart. The same has also been reflected in the table.

Apply Filters 9

NOTE: Always use meaningful names that reflect their functionality or calculation. Avoid complex expressions and break down complex calculations into smaller measures.