Percent contribution for a measure in SAC
In this blog, i will cover another common usecase where you need to display the percent contribution of a particular member of a dimension towards the measure.
Now, you want to see how much each product line is contributing to the sales. You can use %GrandTotal formula in SAC to achieve that.
This is quick and easy. I have also enabled totals to see the total of each column.
Now, let’s filter your table and then see how the % calculation is affected. We filter on crossfit family and note that contribution total still remains 100%
This is not wrong, its just that this is not what you may want. What %Grand Total gives you is the percentage contribution of the Product Line, where total is calculated after applying filters. Hence, the output.
Let’s say you want to keep the total constant for a given selection. For that, you have to calculate total as follows, create a restricted measure
Note that in this case, we have enable constant selection, which eliminates all dimensions from the context to keep the total same, and hence filters will not be applied.
Now, use this total to create a percent calculation “RPercent” by using percent formula
i.e. (Sales * 100)/Total Sales
Now, let’s see what happens when we filter now
This way you can use this constant selection to build calculations where the context of the calculation need to be different from that of selection.