V6.1

Measures and Calculated Columns

Measures and calculated columns are used to perform complex arithmetic calculations on your data. They enable you to extract 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, 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.

In this help article, you will find information on the following,

To Add a Measure

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

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.

From the Data Binding panel on the left, select the DimCustomer table.
Click the Settings icon and then choose the Add Measure option from the list.
In the WAX Expression Editor, enter the expression for the gross profit.
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 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.

From the Data Binding panel on the left, select the 'DimCustomer' table.
Click the Settings icon and then choose the Add Calculated Column option from the list.
In the Expression Editor, enter the expression for the income range.
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.

From the Data Binding panel on the right, select the table which contains the measure or calculated column you want to modify.
Choose the measure or calculated column and click the Settings icon next to it.
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.
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.

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

SelectedValue Function to Filter Values

The SelectedValue function is used to filter out values from the target column. On the Parameter Setting dialog box, click the Variables button and select the SelectedValue option to apply the SelectedValue function to the expression editor. SelectedValue is a front-end expression and the dashboard will replace the expression with an actual value before sending the pivot request to the WAX engine.

The interpolated expression to get filtered data will be replaced by the value when the context for columnName has been filtered down to one distinct value only. Otherwise it returns an alternate result.

Syntax of the SelectedValue function,

#{selectedValue["tableName", "columnName", "alternateResult"]}

The parameters of the SelectedValue function are described in the below table,

Parameter	Description
tableName	The name of an existing table, It cannot be an expression.
columnName	The name of an existing column, it cannot be an expression.
alternateResult	The value returned when the column has no filter or more than 1 distinct value.

Example Scenario:

Create a dashboard with one column chart and a slicer.
Select one value in the slicer like Month Number say 3. This will initiate a data-fetching action and filter out the data for month number 3 in the column chart. SelectedValue function expression will be - #{selectedValue[“FactOnlineSales“, “MonthNumber”, “No single selection“]}.

Wyn Enterprise Documentation