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.
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.
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.
Follow the below steps to edit a measure or a calculated column in an table.
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.
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.
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