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 query model, 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 an entity such as total, count, average, percentage, and more. While calculated columns are used to perform row-by-row calculations on the target entity. 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 entity, namely 'FactOnlineSales' that contains information related to sales, discounts, and returns. We can use this entity to calculate the gross profit for online sales using the below analytical expression.
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' entity.
Let's say, you want to use the 'DimCustomer' entity 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.
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' entity.
Follow the below steps to edit a measure or a calculated column in an entity.
Follow the below steps to delete a measure or calculated column from an entity.
Understanding Analytical Expressions
Measures and Calculated Columns