Calculating New Data Items by Using the Calculate New Measure or Calculate New Column Dialog Box

Overview of Using the Calculate New Measure or Calculate New Column Dialog Box

You can use the Calculate New Measure (or Calculate New Column) dialog box to create new data from your existing data. For example, if your data source has a data item that contains the number of units sold, and another data item that contains the unit price, then you can calculate the total sale amount by multiplying the two data items together.

Calculate New Data Items

To calculate a new data item:
  1. If you are viewing OLAP data, then select Datathen selectCalculate New Measure from the main menu bar.
    If you are viewing relational data, then select Datathen selectCalculate New Column from the main menu bar.
  2. Specify a Name for the new data item.
  3. Create an Expression to define the new data item. For OLAP data, you can either create a custom expression or use a function.
    • Create a custom expression:
      To add a data item to your expression, select it in the Available data items pane and click Insert selection into expression. To add mathematical symbols to your expression, use the following buttons:
      add
      inserts a plus sign (add) into the expression.
      subtract
      inserts a minus sign (subtract) into the expression.
      multiply
      inserts an asterisk (multiply) into the expression.
      divide
      inserts a slash (divide) into the expression.
      group selected operators and operands in the expression
      inserts parentheses (group) into the expression. If you select part of the expression before clicking this button, the parentheses are inserted before and after the selection.
      clear expression
      clears the expression.
      You can also edit your expression manually. This enables you to enter fixed numeric values. For example, to average two data items, add them to the expression, insert a plus sign between them, and add /2 to the expression.
      Note: The name that identifies a data item in an expression might differ from the name that appears in the Available Items pane. To ensure that you use the correct name in your expression, add the data item by clicking Insert selection into expression.
    • Use a function (OLAP data only):
      Note: Functions are available only when a data item in your query contains date or time values.
      To add a function to your expression:
      1. Select a function from the Functions drop-down list:
        Functions for Calculating Time Data
        Function
        Effect
        Difference (consecutive periods)
        calculates the difference between the previous period and the current period.
        Difference (parallel periods)
        calculates the difference between the current period and the previous parallel period. For example, you might calculate the difference between the third quarter of the current year and the third quarter of the previous year.
        Percent Change (consecutive periods)
        calculates the difference between the previous period and the current period, and expresses the difference as a percentage of the previous period's value.
        Percent Change (parallel periods)
        calculates the difference between the current period and the previous parallel period, and expresses the difference as a percentage of the previous period's value. For example, you might calculate the percentage change between the third quarter of the current year and the third quarter of the previous year.
        Rolling Total (period to date)
        calculates the sum of the values for the current period and all of the preceding periods within the same hierarchy level. For example, you might calculate the year-to-date total for each quarter.
        Rolling Total (from selected member)
        calculates the sum of the values for the current period, the selected period, and all of the periods between the selected period and the current period.
      2. If you selected the Rolling Total (from selected member) function, select the starting period from the Starting Period pane.
      3. Select a data item from the Available Data Items pane, and then click Insert selection into expression Icon.
  4. (Optional) Select a data format for the new data item.
    To specify a format:
    1. Click Select Format.
    2. In the Format dialog box, select the type of format from Format type drop-down list.
    3. From the selection box, select the format that you want to use. A description of the selected format is displayed to the right of the selection box.
    4. Specify the width of the format in the Valid width range field. The width that you specify includes every character in the value. For example, $1,978.53 has a width of 9.
    5. Specify the number of decimal places for the format in the Valid decimal range field. For example, 3.141593 has a decimal range of 6.
      Note: The number of decimal places is limited by the width that you specify in the Valid width range field and the number of characters to the left of the decimal. For example, if you specify a width of 8 and a decimal range of 6, the value 346.734947 is represented as 346.7349.
    6. Click OK to apply your format and return to the Calculate New Measure dialog box. The format appears in the Format field.
    You can clear the format by clicking Clear Format.
  5. When you have finished creating your expression, click Add to create the new data item. The new data item appears in the New items pane.
  6. Click OK to add the new data item to your query.
Note: By default, the format for the new data item is the same as the first data item in the calculation expression. However, if the expression uses division, the default format is BEST12.2. If you use a function that uses percentages, the default format is PERCENT. You can specify a different format by clicking Select Format.

Update a Calculated Data Item

To update a calculated data item, follow these steps:
  1. If you are viewing OLAP data, select Datathen selectCalculate New Measure from the main menu bar.
    If you are viewing relational data, select Datathen selectCalculate New Column from the main menu bar.
  2. Select the data item that you want to update from the New items pane. The values for the data item appear in the Name and Expression fields.
  3. Update the values for the data item.
  4. Click Update to replace the data item that is selected in the New items pane.

Delete a Calculated Data Item

To permanently delete a calculated data item, follow these steps:
  1. If you are viewing OLAP data, select Datathen selectCalculate New Measure from the main menu bar.
    If you are viewing relational data, select Datathen selectCalculate New Column from the main menu bar.
  2. Select the data item that you want to delete from the New items pane.
  3. Click Delete to remove the selected data item.
Note: You can also remove a calculated data item from the query without deleting it permanently. For information, see Modifying the Query.