Browsing Analysis Services OLAP Cubes with Excel - Microsoft Business Intelligence Tools for Excel Analysts - page 258

Place your cursor anywhere in the PivotTable and follow these steps:

1. Select MDX Calculated Measure from the OLAP Tools drop-down menu on the Analyze tab.

The New Calculated Measure dialog box opens, as shown in Figure 13-14.

Figure 13-14: Use the New Calculated Measure dialog box to build your calculated measure.

2. Give your calculated measure a name by entering it in the Name text box.

3. Click the Measure Group drop-down menu and select the group you want to place your
calculated measure in.

If you don't choose one, Excel automatically places your measure in the first available measure
group.

4. Enter the MDX syntax for your calculation in the MDX text box.

To save time, you can use the list on the left to select the existing measures you need for your
calculation. Double-click the measures needed, and Excel enters them in the MDX text box.
In this example, the calculation for the average sales price is IIF([Measures].[Order
Quantity] = 0,NULL,[Measures].[Sales Amount]/[Measures].[Order
Quantity]) .

Click the Test MDX button to check if your MDX is well formed. Excel lets you know via a
message box if there is an error in your syntax.

Tip

5. Click OK.

Excel builds your defined measure and adds it to the PivotTable Fields list.

6. Select your newly created calculation from the PivotTable Fields list (see Figure 13-15).