Creating Restricted Measures

You can use the Calculation Editor to create restricted measures.

Prerequisites

A chart or table must be selected.
Note
Restricted measures cannot be created from constant values, or details calculations.

Context

Restricted measures allow you to use only selected values in a measure. Restricted measures can be useful for comparing one value to a set of others in the same chart or table. For example, you can create a measure that contains all expenses for the country of Australia, and compare expenses from Australia side by side with expenses for all other countries.

Procedure

  1. Open the Calculation Editor using one of the following methods:
    • Select a chart and do one of the following:

      • In Builder, choose Start of the navigation pathAdd Measure Next navigation step Calculations Next navigation step Click to Create a New CalculationEnd of the navigation path.

        1. In Builder, beside Chart Structure, select (Add Chart Components), and then select Add Cross Calculations.

          A Cross Calculations section is added with the default dimension Measure Values.

        2. Select Start of the navigation pathSelect Cross Calculation Next navigation step Click to Create a New Cross CalculationEnd of the navigation path.

    • Select a table.

      1. Under Rows or Columns in the Builder tab, select Add Measures/Dimensions and add either the Account or Cross Calculations dimension.

        Tip
        You can rename the Cross Calculations dimension.
      2. For the row or column that you added in the Builder tab, select Start of the navigation path Next navigation step Add CalculationEnd of the navigation path.
    Note
    The option to create a new calculation may not appear if calculations are not possible for the chart type or model.

    The Calculation Editor appears.

  2. Select Restricted Measure from the list.
  3. Enter a name for the restricted measure.
  4. (Optional) Select Constant Selection.

    When Constant Selection is disabled, the restricted measure value is influenced by chart, page, and story filters, as well as categorical axis values. This is the default setting.

    When Constant Selection is enabled, the restricted measure value is determined by the values you specify in the Calculation Editor and will remain constant. Enabling constant selection is useful for comparing a single value with several different values. For example, you could create a restricted measure for sales in 2012, and then compare sales in 2012 with sales for all other years in the same chart.

    Note
    Prompts are respected even when constant selection is enabled.
  5. In the Measure section, select a measure from the list.
  6. In the Dimensions section, select one or more dimensions along which you want to restrict the measure.

    If you want to restrict the measure along more than one dimension, use Add a Dimension.

  7. Beside each dimension, under Values or Input Controls, select Click to Select Values, and then choose an option from the list:
    • PREVIOUS or TO DATE:
      These choices are available when you are restricting the Date dimension. Select a time period under either option to create a dynamic time selection, such as year-to-date (YTD) or previous quarter.
      Note
      • To see this type of restricted measure, you'll need to add the Date dimension to the chart or table, or filter it to a single member.

      • You can also add dynamic time calculations directly to a measure in a chart. See Dynamically Add a Time Calculation for details.

    • Select by Member:

      Select values from the list of available members. If you select Exclude selected members, all members except the ones selected are applied to the restricted measure. You can use (Search) to find specific values. When you expand the list beside the search icon, you may have the following options: Show Description and Show Hierarchy.

      Show Description lets you choose to view the member Description, ID and Description, or ID. Show Hierarchy lets you choose a Flat presentation or an available hierarchy.

      The members you choose appear in the Selected Members list.

    • Select by Range:

      Enter a start value and end value for the range. Select Add a New Range to add additional ranges to the restricted measure.

      Note
      This option appears only if dimension values are numerical or date based. If the dimension is date based, you can also select quarter, month, or year from the slider that appears. Ranges can be fixed or dynamic. For more information, see Story and Page Filters.
    • Create a New Calculation Input Control (This option is available when adding a calculation to a chart.):
      1. Enter a name for the input control, and then select Click to Add Values.

      2. Select values from the list of available members. If you select Exclude selected members, all members except the ones selected will be included in the input control. You can use (Search) to find specific values. When you expand the list beside the search icon, you can choose to view the member Description, ID and Description, or ID.

      3. Expand the Settings for Users section, and then choose whether users can do the following in the input control: Single Selection, Multiple Selection, or Multiple Selection Hierarchy. Select OK.

      4. Select OK.

  8. Select OK.

Results

A measure is created that does not include data for the members that you excluded. Any input controls you created appear on the canvas and are listed in the Calculation Editor under Input Controls. Input controls can also be used in calculated measures.

On the canvas, input controls are indicated by the (Formula) icon. If you hover over the icon, all calculations associated with the input control are displayed. By default, the input control is displayed in token mode where input values can be selected from a drop-down list. The input control can be expanded into widget mode, where radio buttons appear beside each value.