Analytical expressions are primarily used to create measures and calculated columns in Wyn Enterprise.
A model calculation for adding fields to the target table using analytical expressions, which calculates aggregation operations on multiple rows of the table according to the context. The measure is often used to calculate total, count, average, percentage, etc. according to the different dimensions. For example,
SUM([Sales Amount])
A model calculation for adding fields to the target table using analytical expressions. The expression must return a scalar value and is calculated for each row in the table. A calculated column is often used in arithmetic operations or string processing. For example,
[Calendar Year] & " Q" & [Calendar Quarter]
A calculated column has an automatic row context, whereas a measure does not. If you want to evaluate an expression row by row inside a measure, then you need to start an iteration to create a row context. Hence, the same expression is valid when executed for a calculated column, and is invalid if used in a measure.
Let us say that you are working on the 'ContosoRetail' data model that contains information related to online sales, customers, and products.
Here you can add a measure for the 'FactOnlineSales' table to calculate the gross profit for online sales.
SUMX ( FactOnlineSales, FactOnlineSales[SalesAmount] - FactOnlineSales[DiscountAmount] - FactOnlineSales[ReturnAmount] - FactOnlineSales[UnitCost] * ( FactOnlineSales[SalesQuantity] - FactOnlineSales[ReturnQuantity] ) )
Similarly, you can add a calculated column for the 'DimCustomer' table to group the customers based on their salary income.
SWITCH ( TRUE, DimCustomer[YearlyIncome] < 50000, "Low", DimCustomer[YearlyIncome] >= 50000 && DimCustomer[YearlyIncome] < 100000, "Medium", DimCustomer[YearlyIncome] >= 100000, "High" )
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