Dynamically change more than one columns in stories

In this blog I will cover two features of SAC stories that can help you out. Let’s first understand the business scenario that needs to be achieved, and then we can get into details of those features.

Business Scenario

User has two measure groups

  • Group 1 measures – “Sales”, “% of Sales”
  • Group 2 measures – “Order Count”, “% of Order Count”

Now, there is a table that shows “Sales” and “% of Sales” by Customer Neighborhood.

User would like to create an input control with values “Sales” and “Order Count” such that

  • On selection of “Sales” – the table displays “Customer Neighborhood”, “Sales”, “% of Sales”
  • On selection of “Order Count” – the table displays “Customer Neighborhood”, “Order Count”, “% of Order Count”

This is possible in analytics designer applications already via scripting, however in order to achieve that in stories you need to utilize calculation Input Control.

Solution

Create a story, import your main model and first build the following table

Now create a dummy model out of an xls with data as given below. And import that model in that story as well.

In the above table we need two calculated columns such that

  • Absolute – this will either display “Sales” or “Order Count”
  • Percent Contribution – this will either display “% of Sales” or “% of Order Count”

Here is how we create the calculation from the builder panel of the table

While creating the calculation, we will create a calculation input control and use that in the calculation. Here is how you create new calculation input control

While selecting the values in the dialog below, make sure to enable single selection of values.

And, now we will use this calculation Input Control to make a choice on the story page. Before that, we have to finish creating the calculation.

Similarly, create another calculated measure “Percent Contribution”

Now, use these measures in your table.

Note that on selection of Input Control values, both the columns are refelcting the corresponding group measure – Sales or Order Count.