on - edited on by
DRay
Analytical Need
Even though this requirement sounds fairly simple, a lot of times, this use case gets confusing. If there are 10 stores for a Retail Chain, sometimes the Management would only want to compare 3 stores and split the entire contribution within the 3 retail stores.
But there would be instances where the business would want to see the numbers of 3 selected stores but at the same time, would want to see the contribution of each store to the overall sales figure (sum of 10 stores).
In the case of the latter, the selected three stores will be filtered, but the contribution percentage should not be altered only for the 3 stores, but it should be calculated with respect to all the stores of the Retail Chain. Mathematically, the denominator should be the same number (Total Sales across 10 stores) before and after selecting the 3 specific stores.
Modeling Challenge
The first case where the contribution percentage gets altered based on the filtered values, we could make use of the ‘Contribution’ function and achieve the same. Contribution function could be used from the Functions tab or by simply using the ‘Quick Functions’ of the Value menu.
(or)
But to achieve the latter use case, we cannot make use of the ‘CONTRIBUTION’ function but try working this out mathematically.
Solution
MATHEMATICAL SOLUTION:
Irrespective of the filter applied on the Store, the percentage contribution should not change. So,
Percentage Contribution of Store A = Total Sales $ of Store A / (Total Sales of All Stores),
Percentage Contribution of Store B = Total Sales $ of Store B / (Total Sales of All Stores), and so on…
To maintain the percentage contribution unchanged when filtering out few members, we need to have the denominator constant.
SISENSE SOLUTION:
We make use of the ALL function to create the denominator as mentioned in the documentation.
This way, the widget filter to pick specific stores will not affect the percentage contribution value.
So, depending on what the use case is, we could choose which formula needs to be utilized.
