Feature Highlight: Restricted Measures with Constant Selection in SAP Analytics Cloud

Today let’s talk about a combination of two features which allows you to make complex comparisons between different measures: Restricted measures and constant selection. I will first quickly introduce the concept and then show you a few different use cases for it.

The first component is a restricted measure, which means restricting a KPI to a subset based on one or a set of dimension members. An example would be restricting the overall Gross Sales to the Gross Sales of 2018. In that case you would have restricted it by the date dimension on the dimension member 2018. For more details please check out this blog.

The second component is a constant selection of all or a subset of dimensions available in your model. A measure with constant selection will not be impacted by filters or drill-downs on the constant dimensions.

Let’s for example take the restricted measure above, Gross Sales 2018 and add a constant selection across the date dimension.

This measure will not change when you add filters on the date dimension or if you drill down by quarters as you can see in the chart below. When I drill down by the quarters of 2018, the Gross Sales without constant selection will show the sales per quarter but the measure with the constant selection will show the sales for all of 2018 everywhere.

So far so good. But what exactly is this constant selection useful for? Let’s find out in a use case how powerful this feature really is!

Use Case: Comparisons across Versions

Scenario 1: Actuals vs Forecast

In the first scenario we are trying to compare the actuals with the forecast and show the delta in a map. The challenge is that a filter on the Version dimension is mandatory in maps as different versions are not supposed to be added up. Therefore, we need a solution that ignores this filter on actuals.

Map Widget with mandatory Version Filter

Let’s start by restricting Gross Margin to the forecast version. Create a new restricted measure where you use Gross Margin as the base measure and restrict the dimension Version to the member Forecast. Additionally, you check the box for constant selection. Once selected you can choose which dimensions you want to keep constant. In our case it is Version, which is preselected as it is also the dimension you are restricting on. This constant selection is necessary to overrule the map’s Version filter.

In the next step we can calculate the delta. Here we subtract the forecast from the actuals. As the map is filtered on actuals, you can use the measure gross margin as the actuals without restricting it first. Then you subtract our constant forecast dimension from it. You can use this calculation even when the restricted measure is not in use.

To make the result easily readable, we will define a threshold on the delta to show where the actuals are bigger than the forecast which will be shown in green and where they are lower. The latter will be indicated in red. The bubble size will give you an indication of the size of the difference. The result will look like this:

Scenario 2: Comparing Multiple Versions to the Same Base Measure

Another use case could be that you want to compare multiple forecasts and budgets with the actuals of one specific year. In this case we want to compare the Budgets and Forecasts of 2019 and 2020 with the Actuals of 2019 in the same chart as a variance.

In this case, it is not the filters that are posing the challenge but the drill down across the Date and Version dimension which act as local filters in the chart. To be able to add a variance that shows the comparison, we need a measure that ignores the drill-down. This is something you can also achieve with a constant selection.

We need to create a measure that always shows the actuals of 2019, no matter the drill state. We will achieve this by restricting on version actual and the year 2019. Additionally, we need to keep the restricted measure constant for Version and the Date dimension so it will ignore both dimensions in the drill-down.

You could now add this measure into the chart, but we said we would like to have it as a variance for more clarity. For a variance you compare all measures in the chart with the newly created constant measure and get the following result:

I hope this first use case gave you an insight into how to use restricted measures with constant selection. Stay tuned for a few more!

Do you have any interesting use cases for this feature you would like to share or any questions? Please share in the comment section below!

To read about other use cases, check out the following blogs:

• Use Case: Comparison to Exception Aggregation using Restricted Measures with Constant Selection
• Use Case: Dynamically Compare Dimension Members Using Constant Selection