Product Information
Enhance your Analysis with Restricted Measures
A restricted measure in SAP Analytics Cloud is a powerful feature which allows you to add new measures to your stories and enhance your analysis.
In SAP Analytics Cloud, a measure is a term used to describe anything in your dataset that can be counted (add, subtract, multiple, divide, etc.) — profit, revenue, units sold… these are all measures.
As the name implies, restricted measures work by restricting measure values to only show certain data.
In this article, 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 several employees in different regions. We can use a restricted measure calculation to create a count of how many employees we have in each region.
- California
- Nevada
- Oregon
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 to create an aggregation measure to count the number of ‘Sales Associates’.
To create a calculated 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’.
We will then 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. Select Number of Employees as the measure and location as the dimension.
Here, we created a restricted measure singling out, or restricting, California from the list. We need to repeat this workflow for Nevada and Oregon.
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.
Once this is complete, we can edit the format of our calculation in the designer panel by selecting Format.
In the formatting menu, uncheck the “use unit of underlying measures” option so Scale can be updated to percentage 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 the percentage of employees that work in each region. We can see most employees work in multiple states.
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
Navigate to a new chart and create a restricted measure with Gross Margin as the Measure and Location as the Dimension. To exclude Los Angeles and the other states, select the values or input controls field and toggle Exclude Selected measures.
The restricted measure calculation should look like the image below.
- Restrict the gross margin of California to not show any city except Los Angeles
Select Los Angeles and follow the same steps as before.
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 to show 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 Location-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 will start here and show you how to create one in a story.
There are two types of time-based restriction:
- Fixed
- Dynamic
A fixed time-based restriction is when you isolate a certain date such as the month of February, or a year such as 2021. 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.
Let’s create a dynamic time-based restriction by adding 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 Year to Date.
Once we select OK, our chart displays a Year 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 gross margin.
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 outperformed 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 horizontal 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. Revenue)
- Dimension — Select a dimension (i.e. Product 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. Energy Drinks) 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.
First, navigate to your model, select account, and switch display mode to grid.
You will be able to type a formula in the cell and the autofill feature will provide a list of suggested formulas. To open the advanced formula editor, 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. Select the fx button to launch the advanced formula editor.
Similarly, 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.
Alternatively, you can access the advanced formula editor in list mode. Simply navigate to the details button and select a row to open the member details menu. From here, you can open the advanced formula tool.
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, see our additional resources: