Creating Restricted Measures

You can create a measure (or value) that restricts the data from a member of the Account dimension or the Cross Calculations dimension by excluding certain members of one or more dimensions.

Context

Measures (which are referred to as Accounts in a planning model) are numerical values on which you can use mathematical functions.

Restricted measures can be useful for comparing one value to a set of other values 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.

Note
Restricted measures cannot be created from constant values, or details calculations.

Procedure

  1. Open the Calculation Editor using one of the following methods:
    Option Description
    Select a Chart
    Do one of the following:

    • In Builder, choose Start of the navigation pathAdd Measure Next navigation step Calculations Next navigation step Create 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 table, 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:
    • Start of the navigation pathSELECTION Next navigation step Select by MemberEnd of the navigation path:

      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.

    • Start of the navigation pathSELECTION Next navigation step Select by RangeEnd of the navigation path:

      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.

      In some workflows, for example planning, you might want to set the current date of a dynamic date range filter to be different from today's actual date. To learn how to do this, see Customizing the Current Date.

    For date dimensions, you can quickly specify a dynamic restriction based on the current date using these options:

    • Start of the navigation pathNAVIGATION Next navigation step Previous (shift previous value)End of the navigation path:

      This option shifts the values from a previous time period, letting you show them side-by-side with the current values. For example, you might select Year to see how your monthly values in the current year compare with the same months from the previous year.

    • Start of the navigation pathNAVIGATION Next navigation step To Date (running total)End of the navigation path:

      This option shows a running total of the values over the time period that you select. For example, you might select Year to see the year-to-date (YTD) values for each month next to the monthly values.

    • Start of the navigation pathSELECTION Next navigation step Current PeriodEnd of the navigation path:

      This option sets a dynamic range filter that selects the current period. Choose Full Period to select all dates of the current period, including those in the future. Choose To Date to select dates from the start of the current period up to the current date. Next, choose the granularity.

    • Start of the navigation pathSELECTION Next navigation step Previous PeriodEnd of the navigation path:
      This option sets a dynamic range filter that selects the previous period. Choose Full Period to select all dates of the previous period. Choose To Date to create a filter such as year to date that’s offset to the previous period. Next, choose the granularity.
      Note

      • To see restricted measures with dynamic date range selections, 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.

    • 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.

Related Information