Measures and Calculated Columns

Measures and calculated columns are used to perform complex arithmetic calculations on your data. They enable you to build new data from the existing data, available in the datasets and data models, with the help of analytical expressions. An analytical expression consists of functions, operators, functions, operators, and value references, which are evaluated as a formula to generate results.

In general, measures are used to perform aggregate operations on the table such as total, count, average, percentage, and more. While calculated columns are used to perform row-by-row calculations on the target table. Note that measures and calculated columns can only reference attributes in analytical expressions. To learn more about measures and calculated columns, please see this article.

To Add a Measure

Consider a sales table, namely 'FactOnlineSales' that contains information related to sales, discounts, and returns. We can use this table to calculate the gross profit for online sales using the below analytical expression.

SUMX (
    FactOnlineSales,
    FactOnlineSales[SalesAmount] - FactOnlineSales[DiscountAmount] - FactOnlineSales[ReturnAmount] - FactOnlineSales[UnitCost] * ( FactOnlineSales[SalesQuantity] - FactOnlineSales[ReturnQuantity] )
)

Follow the below steps to add a measure with the above expression to the 'FactOnlineSales' table.

1. From the Data Binding panel on the left, select the 'DimCustomer' table.
2. Click the Settings icon and then choose the Add Measure option from the list.
   
3. In the Expression Editor, enter the expression for the gross profit.
   
4. Then, click the OK button.
   The measure now appears under the chosen table in the Data Binding panel along with its other attributes.
   

To Add a Calculated Column

Let's say, you want to use the 'DimCustomer' table to divide the customers into groups based on their yearly income. To achieve this, use the following expression for the calculated column, which will categorize the customers into 'Low', 'Medium', and 'High' according to their yearly income.

SWITCH (
    TRUE,
    DimCustomer[YearlyIncome] < 50000, "Low",
    DimCustomer[YearlyIncome] >= 50000 && DimCustomer[YearlyIncome] < 100000, "Medium",
    DimCustomer[YearlyIncome] >= 100000, "High"
)

Follow the below steps to add a calculated column with the above expression to the 'DimCustomer' table.

1. From the Data Binding panel on the left, select the 'DimCustomer' table.
2. Click the Settings icon and then choose the Add Calculated Column option from the list.
   
3. In the Expression Editor, enter the expression for the income range.
   
4. Then, click the OK button.
   The calculated column now appears under the chosen table in the Data Binding panel along with its other attributes.
   

To Edit a Measure or a Calculated Column

Follow the below steps to edit a measure or a calculated column in an table.

1. From the Data Binding panel on the right, select the table which contains the measure or calculated column you want to modify.
2. Choose the measure or calculated column and click the Settings icon next to it.
   

3. If you want to enter a new expression or update the existing expression for the measure or calculated column, choose the Edit Expression option. The Expression Editor appears as shown below.
   

   Note: You cannot edit or update the expression for a data model measure or calculated column.

   

4. If you want to modify the format of the measure or calculated column, choose the Edit Format option. The Format Field dialog box appears as shown below.
   
   For more information on data format, please see this article.

To Delete a Measure or a Calculated Column

Follow the below steps to delete a measure or calculated column from the bound model.

Note: You cannot remove or delete a data model measure or calculated column in the dashboard designer.

1. From the Data Binding panel on the right, select the table which contains the measure or calculated you no longer require.
2. Choose the measure or calculated column and click the Settings icon next to it.
   
3. Select the Remove option from the list.
   The chosen measure or calculated column is now deleted.
   

See Also

    Understanding Analytical Expressions
    Measures and Calculated Columns in Dashboards
    Add Measures and Calculated Columns in Direct Query Model
    Add Measures and Calculated Columns in Cached Model