Developing a Calculated Measure

Overview

Measures created directly from the fields of a fact table are called base measures. But often we require measures based on custom requirements, so we apply some logic and/or formula to these base measures and create calculated measures. We will add two measures from two measure groups and create a calculated measure.

Explanation

Open the cube designer, and click on the Calculations tab. Click on “New Calculated Measure” from the toolbar, and key in the values as shown in the below screenshot.



We have named this new calculated measure “TotalSales”. The "Parent hierarchy" specifies which parent hierarchy the measure will be part and in this case it will be “Measures”. It’s a built-in hierarchy and all measures normally fall under this.

In the Expression, we can specify any MDX expression. Here we are adding Internet Sales Amount from FactInternetSales and Reseller Sales Amount from FactResellerSales measure groups. You do not need to type the values you can just drag and drop values from the panes on the left-hand side of the window.

In the additional properties you can set additional options for this measure. Save your solution, in the next section we will create named sets and then deploy these at the same time.

Source Collected from MSSQLTIPS.COM