Technical Articles
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.
Great post.
Any idea how to make it calculate dynamically for each product group (Juices, Bikes, etc.)?
Hi Jef,
Interesting question, if you'd want this behaviour you could prepare multiple Restricted measures and have % Share being a conditional IF calculation based on product groups.
My inspiration for above example actually did not want this behavior, as when they filtered on a given product group they still wanted the % Share relative to the entire total and not a subset.
Something worth investigating for sure!
Nick
Hi Nick
Thanks for the great blog!
Is there a way to cross calculate on aggregation and calculation measures instead of restricted measures?
For example, when you build a revenue table
(1) Make the data in the row a calculated measure. (Sales = unit price * quantity)
(2) Make the data in the columns a dynamic date period. (Current period, previous period)
(3) Calculate with each other. (Ratio to previous period = Current period / previous period * 100)
When I tried, I didn't get the right number in (3).
I'm sorry for the impatient English. Please tell me if you have any ideas.
Tomoki
Hi Tomoki,
I tried to recreate your issue but did not succeed.
By adding a calculated measure for Revenue and creating a restricted measure for prv period where constant selection is not needed, subsequently adding a ratio calculation gives me the results as expected?
Example calc
Hi Nick
Thanks for the polite explanation.
Having done as you were told, I could solve it!
Best.
Tomoki