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
-
Open the Calculation Editor using one of the following
methods:
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.
-
Select Restricted Measure from the list.
-
Enter a name for the restricted measure.
-
(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.
-
In the Measure section, select a measure from the list.
-
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.
-
Beside each dimension, under Values or Input Controls, select
Click to Select Values, and then choose an option
from the list:
- :
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.
- :
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:
- :
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.
- :
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.
- :
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.
- :
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.):
-
Enter a name for the input control, and then select
Click to Add Values.
-
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.
-
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.
-
Select OK.
-
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.