Custom Measures

Custom measures are used to produce values that may not be available directly in the Prepare instance — for example, a profit percentage calculated from sales and cost fields. Custom measures use a script to calculate the measure value.

Add a custom measure

1. Right-click a table in the Deliver instance and select Add Custom Measure to open the custom measure editor.

   

2. In the Name box, enter a name for the measure.
3. In the Description box, enter a description of the custom measure. This may be displayed in front-end tools.
   Note:

   Description applies to SSAS Tabular and Power BI Premium endpoints only.

4. In the Data type list, select the data type for the custom measure.
5. In the Data format list, select the data format. For some data types, only one format is available. Click Customize to adjust the format — for example, to set the number of decimal places for a decimal number.
   Note:

   Data format applies to SSAS Tabular and Power BI Premium endpoints only.

6. In the Script box, enter the script that calculates the value of the custom measure. Because script syntax differs between endpoint types, a separate script can be added for each endpoint type. Click an endpoint type in the Endpoints list to switch between endpoint types. Any endpoint type without a specific script uses the Default script. Drag fields from the Available parameters list into the Script pane to use them as parameters. Click Show translation to preview the script that will be deployed to the endpoint.

Example: Profit % custom measure

The following example shows how to configure a Profit % custom measure that calculates profit percentage from Sales_Amount_Actual and Cost_Amount_Actual fields.

Default endpoint configuration

• Data type: Decimal
• Data format: Percentage — automatically converts the decimal result to a percentage, so no manual percentage conversion is needed in the script. Customize is set to two decimal places.

  

• The two fields used in the script are dragged from the Available parameters pane into the Script window.
• The Variant setting for Cost_Amount_Actual is changed from Qualified Semantic field name to Fully qualified Semantic field name. Clicking Show translation displays the updated fully qualified parameter name.

  

• The Variant setting for Sales_Amount_Actual is left as Qualified Semantic field name, showing how the two variants differ in the script output.

Tabular and Power BI Premium endpoint configuration

Two previously created measures are dragged into the Script window. The Sales measure aggregates the SUM of Sales_Amount_Actual, and the Profit measure is calculated as Sales − Cost. The Profit % measure then uses these two measures to calculate the percentage.