Use Case: Having a dynamic % Share of total using Restricted Measures with Constant Selection

Welcome everybody to the fourth use case for using Constant Selection. If you have never heard of a Constant Selection and would like to understand the basics first check out our introductory blog.

In this instance of our blog series on Constant Selection we’ll focus on the use of Constant Selection to the benefit of showing the related percentage to the totals.

Imagine now you are responsible for planning quantities in a given product category of beverages, juices. – Fun fact, “juice” roughly translates to “sap” in Dutch – A simple table like below would fit my use case to allow the planner to plan for 2021 and 2022.

First thing as a planner it would be helpful to understand the percentage share of each product in its category. I can not use the %Grandtotal functionality because the percentage is only interesting for the corresponding time period, not across all time periods. This is where the powerful flexibility of Constant Selection can help. To do so let’s create a Restricted Measure with Constant Selection toggle enabled to get the total for the products by year, we’ll name the measure TotalProduct. – Note that we don’t really restrict any values to get the total returned –

In the table we’ll see something like below where you can clearly see TotalProduct is repeating the total for each row, thus we can use it in a subsequent calculation for the total.

Now we can use TotalProduct to calculate the percentage share which will now dynamically react to drilling behavior in time periods.

Note how the % Share and TotalProduct calculations react dynamically after drilling from the year to the quarters.

To complete the formatting, we’ll make sure the % Share measure is actually formatted as a percentage and the TotalProduct measure can be removed from the table.

Adding some figures in our planning versions to reflect what we expect to happen in the Juice category of our product line, now aided by the additional % Share calculation.

Finally, as icing on the cake we’ll add some visuals so we can easily analyze which products are growing and declining over time, as well as how they relate to the total. – Apples are the clear winner in the story below –

This is only a starting point on how to use this functionality, as with using Restricted Measures and Constant Selection you are in complete control to show what is relevant to you flexibly, how do you like them apples!

Can’t get enough of Constant Selection? Check out these other blogs by Anna Burkhardt:

Use Case: Dynamically Compare Dimension Members Using Constant Selection

Use Case: Comparison to Exception Aggregation using Restricted Measures with Constant Selection

Use Case: Comparison to Exception Aggregation using Restricted Measures with Constant Selection

Footnote: This blog is potentially heavily biased by the snacks consumed at the time of writing.