Create Restricted Measures for Tables

In your SAP Analytics Cloud table, you can create a measure (or account) 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 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.

Procedure

  1. Select a table and open the Calculation Editor.
    To open the Calculation Editor, do the following:

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

    Note
    Prompts are respected even when constant selection is enabled.
  5. In the Measure section, select a measure from the list.
    Remember

    When searching for a specific measure in a long list of measures, you must use the measure's description, not its ID, as your search term.

  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 week, 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 table, or filter it to a single member.

    • Create a New Calculation Input Control (This option is available when adding a calculation to a table.):

      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