Deliver instance tables represent the culmination of all the extraction, transformation, and loading that happens prior to the tables being added to the Deliver instance. 

There are various types of fields that can be added to a Deliver instance. This article clarifies how to add a custom measure to a Deliver instance table.

Custom measures are used to produce values that may not be available directly in the Prepare instance, such as the profit measure used in the example below. 

Adding a Custom Measure

Custom measures use a script to calculate the value of the measure. 

Use the following steps to add a custom measure.

1. Right-click a table and select Add Custom Measure to open the following window.

   

    

2. In the Name box, enter a name for the field.
3. In the Description box, type a description of the custom measure, which may be available in front-end tools.

   Note: This setting applies to SSAS Tabular and PowerBI Premium endpoints only.

4. In the Data type list, click on the data type to be used for the custom field.
5. In the Data format list, select the data format to be used for the field. For some data types, only one data format is available and this cannot be changed. Click Customize to customize the data format, e.g. number of decimal places for decimal numbers.

   Note: This setting applies to SSAS Tabular and PowerBI Premium endpoints only.

6. In the Script box, enter the script that generates the value for the custom measure. Since script syntax is not the same for different endpoint types, it is possible to add separate scripts for each type of endpoint. Click on an endpoint type in the Endpoints list to switch between the different endpoint types. Any endpoint type that does not have a specific script will use the Default script. Drag fields from the Available parameters list to the Script pane to use them as parameters in the script. Click Show translation to show the script TimeXtender will deploy to the endpoint.

How to set up a custom measure

The following “Profit %” custom measure calculates the profit percentage based on the “Sales_Amount_Actual” and “Cost_Amount_Actual” fields. 

The Default endpoint is configured in the Edit Custom Measure below.

1. The Data type is set to Decimal
2. The Data format is set to Percentage, which will convert the decimal result to a percentage, so this does not need to be done manually using a script. Customize is set to have two Decimal places as follows: 

   

    

3. The two fields used in the Script window have been dragged over from the Available parameters pane on the right.
4. After being added, the parameters are listed out below. In this example, the Variant setting for the Cost_Amount_Actual parameter has been changed from Qualified Semantic field Name to Fully qualified Semantic field name. Clicking Show translation will show the updated fully qualified parameter name as follows:

   

    

5. The Variant parameter setting for the Sales_Amount_Actual field has been set to Qualified Semantic field Name to show how this affects the script.

The set up for the Tabular and PowerBI Premium endpoint is shown in the following window:  

Two measures that were previously created have been dragged into the Script window. The Sales measure aggregates the SUM of the Sales_Amount_Actual, and the Profit measure is calcuated as Sales - Cost.

​