Defining Calculated Members
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Calculated members are members of a dimension or a measure group that are defined based on a combination of cube data, arithmetic operators, numbers, and functions. For example, you can create a calculated member that calculates the sum of two physical measures in the cube. Calculated member definitions are stored in cubes, but their values are calculated at query time.
To create a calculated member, use the New Calculated Member command on the Calculations tab of Cube Designer. You can create a calculated member within any dimension, including the measures dimension. You can also place a calculated member within a display folder in the Calculation Properties dialog box. For more information, see Calculations, Calculations in Multidimensional Models, and Create Calculated Members.
In the tasks in this topic, you define calculated measures to let users view the gross profit margin percentage and sales ratios for Internet sales, reseller sales, and for all sales.

Open Cube Designer for the Analysis Services Tutorial cube, and then click the Calculations tab.
Notice the default CALCULATE command in the Calculation Expressions pane and in the Script Organizer pane. This command specifies that the measures in the cube should be aggregated according to the value that is specified by their AggregateFunction properties. Measure values are generally summed, but may also be counted or aggregated in some other manner.
The following image shows the Calculations tab of Cube Designer.

On the toolbar of the Calculations tab, click New Calculated Member.
A new form appears in the Calculation Expressions pane within which you define the properties of this new calculated member. The new member also appears in the Script Organizer pane.
The following image shows the form that appears in the Calculation Expressions pane when you click New Calculated Member.

In the Name box, change the name of the calculated measure to [Total Sales Amount].
If the name of a calculated member contains a space, the calculated member name must be enclosed in square brackets.
Notice in the Parent hierarchy list that, by default, a new calculated member is created in the Measures dimension. A calculated member in the Measures dimension is also frequently called a calculated measure.

On the Metadata tab in the Calculation Tools pane of the Calculations tab, expand Measures and then expand Internet Sales to view the metadata for the Internet Sales measure group.
You can drag metadata elements from the Calculation Tools pane into the Expression box and then add operators and other elements to create Multidimensional Expressions (MDX) expressions. Alternatively, you can type the MDX expression directly into the Expression box.
Note If you cannot view any metadata in the Calculation Tools pane, click Reconnect on the toolbar. If this does not work, you may have to process the cube or start the instance of Analysis Services.

Drag Internet SalesSales Amount from the Metadata tab in the Calculation Tools pane into the Expression box in the Calculation Expressions pane.

In the Expression box, type a plus sign (+) after [Measures].[Internet SalesSales Amount].

On the Metadata tab in the Calculation Tools pane, expand Reseller Sales, and then drag Reseller SalesSales Amount into the Expression box in the Calculation Expressions pane after the plus sign (+).

In the Format string list, select "Currency".

In the Nonempty behavior list, select the check boxes for Internet SalesSales Amount and Reseller SalesSales Amount, and then click OK.
The measures you specify in the Nonempty behavior list are used to resolve NON EMPTY queries in MDX. When you specify one or more measures in the Nonempty behavior list, Analysis Services treats the calculated member as empty if all the specified measures are empty. If the Nonempty behavior property is blank, Analysis Services must evaluate the calculated member itself to determine whether the member is empty.
The following image shows the Calculation Expressions pane populated with the settings that you specified in the previous steps.

On the toolbar of the Calculations tab, click Script View, and then review the calculation script in the Calculation Expressions pane.
Notice that the new calculation is added to the initial CALCULATE expression; each individual calculation is separated by a semicolon. Notice also that a comment appears at the beginning of the calculation script. Adding comments within the calculation script for groups of calculations is a good practice, to help you and other developers understand complex calculation scripts.

Add a new line in the calculation script after the Calculate; command and before the newly added calculation script, and then add the following text to the script on its own line:
/* Calculations to aggregate Internet Sales and Reseller Sales measures */
The following image shows the calculation scripts as they should appear in the Calculation Expressions pane at this point in the tutorial.

On the toolbar of the Calculations tab, click Form View, verify that [Total Sales Amount] is selected in the Script Organizer pane, and then click New Calculated Member.

Change the name of this new calculated member to [Total Product Cost], and then create the following expression in the Expression box:
[Measures].[Internet SalesTotal Product Cost] + [Measures].[Reseller SalesTotal Product Cost]

In the Format string list, select "Currency".

In the Nonempty behavior list, select the check boxes for Internet SalesTotal Product Cost and Reseller SalesTotal Product Cost, and then click OK.
You have now defined two calculated members, both of which are visible in the Script Organizer pane. These calculated members can be used by other calculations that you define later in the calculation script. You can view the definition of any calculated member by selecting the calculated member in the Script Organizer pane; the definition of the calculated member will appear in the Calculation Expressions pane in the Form view. Newly defined calculated members will not appear in the Calculation Tools pane until these objects have been deployed. Calculations do not require processing.

Verify that [Total Product Cost] is selected in the Script Organizer pane, and then click New Calculated Member on the toolbar of the Calculations tab.

In the Name box, change the name of this new calculated measure to [Internet GPM].

In the Expression box, create the following MDX expression:
([Measures].[Internet SalesSales Amount]  [Measures].[Internet SalesTotal Product Cost]) / [Measures].[Internet SalesSales Amount]

In the Format string list, select "Percent".

In the Nonempty behavior list, select the check box for Internet SalesSales Amount, and then click OK.

On the toolbar of the Calculations tab, click New Calculated Member.

In the Name box, change the name of this new calculated measure to [Reseller GPM].

In the Expression box, create the following MDX expression:
([Measures].[Reseller SalesSales Amount]  [Measures].[Reseller SalesTotal Product Cost]) / [Measures].[Reseller SalesSales Amount]

In the Format string list, select "Percent".

In the Nonempty behavior list, select the check box for Reseller SalesSales Amount, and then click OK.

On the toolbar of the Calculations tab, click New Calculated Member.

In the Name box, change the name of this calculated measure to [Total GPM].

In the Expression box, create the following MDX expression:
([Measures].[Total Sales Amount]  [Measures].[Total Product Cost]) / [Measures].[Total Sales Amount]
Notice that this calculated member is referencing other calculated members. Because this calculated member will be calculated after the calculated members that it references, this is a valid calculated member.

In the Format string list, select "Percent".

In the Nonempty behavior list, select the check boxes for Internet SalesSales Amount and Reseller SalesSales Amount, and then click OK.

On the toolbar of the Calculations tab, click Script View and review the three calculations you just added to the calculation script.

Add a new line in the calculation script immediately before the [Internet GPM] calculation, and then add the following text to the script on its own line:
/* Calculations to calculate gross profit margin */
The following image shows the Expressions pane with the three new calculations.

On the toolbar of the Calculations tab, click Form View.

In the Script Organizer pane, select [Total GPM], and then click New Calculated Member on the toolbar of the Calculations tab.
Clicking the final calculated member in the Script Organizer pane before you click New Calculated Member guarantees that the new calculated member will be entered at the end of the script. Scripts execute in the order that they appear in the Script Organizer pane.

Change the name of this new calculated member to [Internet Sales Ratio to All Products].

Type the following expression in the Expression box:
Case When IsEmpty( [Measures].[Internet SalesSales Amount] ) Then 0 Else ( [Product].[Product Categories].CurrentMember, [Measures].[Internet SalesSales Amount]) / ( [Product].[Product Categories].[(All)].[All], [Measures].[Internet SalesSales Amount] ) End
This MDX expression calculates the contribution to total Internet sales of each product. The Case statement together with the IS EMPTY function ensures that a divide by zero error does not occur when a product has no sales.

In the Format string list, select "Percent".

In the Nonempty behavior list, select the check box for Internet SalesSales Amount, and then click OK.

On the toolbar of the Calculations tab, click New Calculated Member.

Change the name of this calculated member to [Reseller Sales Ratio to All Products].

Type the following expression in the Expression box:
Case When IsEmpty( [Measures].[Reseller SalesSales Amount] ) Then 0 Else ( [Product].[Product Categories].CurrentMember, [Measures].[Reseller SalesSales Amount]) / ( [Product].[Product Categories].[(All)].[All], [Measures].[Reseller SalesSales Amount] ) End

In the Format string list, select "Percent".

In the Nonempty behavior list, select the check box for Reseller SalesSales Amount, and then click OK.

On the toolbar of the Calculations tab, click New Calculated Member.

Change the name of this calculated member to [Total Sales Ratio to All Products].

Type the following expression in the Expression box:
Case When IsEmpty( [Measures].[Total Sales Amount] ) Then 0 Else ( [Product].[Product Categories].CurrentMember, [Measures].[Total Sales Amount]) / ( [Product].[Product Categories].[(All)].[All], [Measures].[Total Sales Amount] ) End

In the Format string list, select "Percent".

In the Nonempty behavior list, select the check boxes for Internet SalesSales Amount and Reseller SalesSales Amount, and then click OK.

On the toolbar of the Calculations tab, click Script View, and then review the three calculations that you just added to the calculation script.

Add a new line in the calculation script immediately before the [Internet Sales Ratio to All Products] calculation, and then add the following text to the script on its own line:
/* Calculations to calculate percentage of product to total product sales */
You have now defined a total of eight calculated members, which are visible in the Script Organizer pane when you are in Form view.

On the Build menu of SQL Server Data Tools (SSDT), click Deploy Analysis Services Tutorial.

When deployment has successfully completed, switch to the Browser tab, click Reconnect.

Click the Excel icon, and then click Enable.

In the PivotTable Field List pane, expand Values folder to view the new calculated members in the Measures dimension.

Drag the Total Sales Amount to the Values area, and then review the results.
Drag Internet SalesSales Amount and Reseller SalesSales Amount measures from the Internet Sales and Reseller Sales measure groups to the Values area.
Notice that the Total Sales Amount measure is the sum of the Internet SalesSales Amount measure and the Reseller SalesSales Amount measure.

Add the Product Categories userdefined hierarchy to the filter area of the Report Filter area, and then filter the data by Mountain Bikes.
Notice that the Total Sales Amount measure is calculated for the Mountain Bikes category of product sales based on the Internet SalesSales Amount and the Reseller SalesSales Amount measures for Mountain Bikes.

Add the Date.Calendar Date userdefined hierarchy to the Row labels area, and then review the results.
Notice that the Total Sales Amount measure for each calendar year is calculated for the Mountain Bikes category of product sales based on the Internet SalesSales Amount and the Reseller SalesSales Amount measures for Mountain Bikes.

Add the Total GPM, Internet GPM, and Reseller GPM measures to the Values area, and then review the results.
Notice that the gross profit margin for reseller sales is significantly lower than for sales over the Internet, as shown in the following image.

Add the Total Sales Ratio to All Products, Internet Sales Ratio to All Products, and Reseller Sales Ratio to All Products measures to the Values area.
Notice that the ratio of the sales of mountain bikes to all products has increased over time for Internet sales, but is decreasing over time for reseller sales. Notice also that the ratio of the sale of mountain bikes to all products is lower from sales through resellers than it is for sales over the Internet.

Change the filter from Mountain Bikes to Bikes, and review the results.
Notice that the gross profit margin for all bikes sold through resellers is negative, because touring bikes and road bikes are being sold at a loss.

Change the filter to Accessories, and then review the results.
Notice that the sale of accessories is increasing over time, but that these sales make up only a small fraction of total sales. Notice also that the gross profit margin for sales of accessories is higher than for bikes.