A restricted measure in SAP Analytics Cloud is a powerful feature that allows you to add new measures to your stories and enhance your analysis.
In SAP Analytics Cloud, a ‘measure’ is a term we use to describe anything in your dataset that you can count (add, subtract, multiple, divide, etc.) — profit, revenue, units sold… these are all measures.
As the name implies, restricted measures work by restricting part of your measure to only show certain data.
In this post, we cover a few examples of how you can use restricted measures to enhance your analysis, such as:
- Restricting a dimension
- Location-based restricted measure
- Time-based restricted measure
- Employee-based restricted measure
- Adding an Input Control
- Creating a restricted measure in modeler
Restricting a dimension
In our dataset, we have a number of employees in different regions. We can use a restricted measure calculation to create a count of how many employees we have in each region.
We can also create another calculation and divide each group by the total. This will give us a breakdown of the percentage of employees within each region.
Since ‘Sales Associates’ are a dimension not a measure, we need create an aggregation so that they become a measure.
First, we create an new calculation, select Aggregation as our type, Count Dimensions as our Operation, and Sales Associates as our Aggregation Dimension. We can give it a name such as ‘Number of Employees’. This way, when we create our restricted measure, we’ll have a measure to select.
Next, we need to create three restricted measure calculations.
Here, we created a restricted measure singling out, or restricting, Montreal from the list. We need to repeat this workflow for Vancouver and Toronto.
We can also create a Calculated Measure calculation to divide one city’s number of employees by the total number of employees to get a percentage measure. Again, we can do this for each city. This way, we can see what percentage each city makes up in the total number of employees.
We may want to edit the formatting of our calculation so that the percentage displays with 0 decimal places.
Finally, we can add an input control so that we can select each of the regions.
The result is a visualization that shows where our employees are located by region.
Creating a location-based restricted measure in a story
In this next example, we have location data for the state of California. There may be scenario where we want to remove, or restrict our analysis to exclude one city from the rest of the data.
So let’s do that. Suppose we want to isolate the gross margin of Los Angeles from the rest of California. This will give us a better understanding of how Los Angeles is performing, as well as how the rest of California is performing without Los Angeles.
First, we need to create two restricted measures:
- Restrict the gross margin of California to show all cities except Los Angeles
- Restrict the gross margin of California to not show any city except Los Angeles
The result is two new measures being created:
- California minus Los Angeles
- Los Angeles
We can now use these new measures to create a comparison chart. We can also add a calculation which shows what percentage of California’s Gross Margin is a result of the city of Los Angeles.
With Los Angeles isolated from the rest of California, we can see what percentage of California’s gross margin is made up by the city of Los Angeles.
Creating a time-based restricted measure in a story
Restricted measures may be created within a story, or they can be created in the modeler. When created in the modeler, they are available to use in any story based on that model.
Since time-based restricted measures are so common, we’ll start here and show you how to create one in a story.
There are two types of time-based restriction:
A fixed time-based restriction is when you isolate a certain date such as the month of February, or a year such as 2017. They’re fixed because these dates won’t change.
An example where this can be used is when a company has unusually high revenue for a particular month that was a result of a merger or acquisition, or some other anomaly that doesn’t occur regularly. You may want to remove that month from your analysis by creating a restricted measure.
A dynamic time-based restriction is when you restrict your time based on a moving time frame such as year to date, quarter to date, or month to date. It’s dynamic because these dates change depending on when you access your story.
To create a time-based restricted measure, select a chart in your story, then navigate to the Designer > Builder panel. Under Measures, select ‘+ Add Measure’ > ‘+ Click to Create a New Calculation’.
In the Calculation Editor, choose the measure (i.e. Quantity Sold), and choose Date as the Dimension. For the ‘Values or Input Controls’, we’ve selected Month to Date.
Once we select OK, our chart displays a Month to Date measure.
Creating an employee-based restricted measure in a story
Another useful way we to use restricted measures in our stories is to compare employee performance. In our dataset, we have a list of Sales Managers and their yearly Sales Revenue.
We have three top performers:
- Kiran Raj
- David Carl
- Janet Bury
For our purposes, let’s say that in the previous year, Janet Bury was our top performer. So we want to use her as our baseline, and see how the rest of our Sales Managers compare to her this year.
We already know Kiran and David out performed her, but we want to know by what percentage. We also want to see the percentage breakdown of how she outperformed the rest of the Sales Managers.
To do this, we need to create a restricted measure to isolate Janet Bury from the rest of the Sales Managers. This allow us to use that new measure for our calculation.
We need to select ‘Constant Selection’ as well as set the category to actual.
Now that we have Janet Bury as her own measure, we’re ready to create our calculation. We take (gross margin – Janet Bury) / Janet Bury. This will set Janet Bury as the benchmark in which all other Sales Managers will be measured against.
We also need to edit the formatting so that it shows the percentage.
By selecting a vertical bar chart, we can visually represent our data in a clean manner.
With Janet Bury representing the baseline, we can easily see which of our Sales Managers outperformed her and which ones did not. Since we chose to display the figures in a percentage, we can see the percentage of how much or how little the other Sales Managers rank compared to her.
Adding an Input Control with a restricted measure
We can further refine our analyses by adding a restricted measure input control. This enables us to select different members in our data we want analyze.
For example, suppose we want to find out which of our Sales Managers have the highest Sales Revenue with certain products in certain regions.
We can create a restricted measure input control to allow us to restrict our visualization depending on the product and location we want to see.
First, we navigate to the Builder panel and select ‘+Create Calculation…’.
In the calculation editor, choose the following
- Type — Restricted Measure
- Measure — Choose a measure (i.e. Sales Revenue)
- Dimension — Select a dimension (i.e. Category)
- Name — Give your calculation a name
- Values or Input Controls — New Calculation Input Control
In the Calculation Input Control, select the values you want available in your Input Control.
Since we want the ability to choose different members at different times, we’ll select ‘All Members’.
In our chart, we can also add a location Input Control.
When combined, we have a powerful filtering tool that allows us to control the inputs on our visualization. In the example above, our visualization has been filtered to show the Sales Managers’ Sales Revenue by product (i.e. Alcohol) and by State (i.e. Nevada).
Creating a restricted measure in modeler
The advantage of creating restricted measures in modeler is that you can use them in multiple stories that are based on that one model.
Any calculations done at the story level are only available in that story.
In the modeler, select an empty cell in the Formula column and choose between two options:
- The Formula bar
- Advanced Formula Editor
The Formula bar
The Formula bar enables you to enter your formula or choose from the predefined formulas. To access a list of suggested formulas, begin typing or press Ctrl + Space bar.
The Advanced Formula Editor
The Advanced Formula Editor gives you a few more options and allows you to easily add functions, conditions, and operators into your formula.
Similarly to the Formula bar, you can begin typing your formula and the autofill feature will provide you with relevant options.
We want to create a formula to restrict the state of Nevada from our dataset.
In this example, we’ve opted to restrict the gross margin for all the states except Nevada — RESTRICT ( GrossMargin, [d/state] = “Nevada” ).
The result is a new Nevada measure is created, which we can use in any story based on this model.
There are plenty of other ways restricted measures can be used to enhance your data visualization. To learn more about creating restricted measures in either stories or modeler, please check out our additional resources.