How to Make sum(summary(…)) Dynamic Based on Filtered Countries?
Hi everyone,
I’m using a formula like this in my model:
Sum of Europe = sum(summary(Europe))
Where Europe is a category and inside the category I have car sales for Italy, France etc.
The issue is that this total does not update when I filter countries. For example, the total sum of Europe is 1000, but when I filter out Italy it stays 1000, when it should be 1000 – X(Italy).
I want the formula to sum only the selected items in the category of Europe.
Does anybody know how to do it?
Many thanks!
Hi Balo,
Just a quick note – when you filter or collapse items, it only changes how things look. It does not affect any calculations.
If you want your summary calculations to actually respond to filters, you will need to set up a separate matrix. This lets you choose which countries to include.
Sau already shared a great solution with advanced functions. Here is a simpler alternative how to do it:
- Create a new matrix and call it “Filter”.
- Drag and drop the country category to that matrix.
- Use a Boolean constraint input (1 if selected, 0 if not) on values of those country items. That will give you checkboxes so you can select which countries should be part of the summary.
In your “Sales” matrix, take your summary function and multiply it by the country item values in “Filter” matrix. Here is important to skip the last summary item by using recursion: Country[FIRST] .. Country[LAST-1].
That way, the summary updates based on your country selections.
Best,
Tahir / Smart Modeling Solutions
Hi All!
Balo45, below is my way to solve your problem. I introduced a Filter control matrix in which I manually set flags (FOFs) for countries that need to be excluded from summation. Based on the values of FOF flags, I form a list of names of countries to be included in the summation by means of formula:
*
*
Now I do two steps in the matrix with sales data. In the first step I apply a cell filter by expression: not(Filter::FOF). In the second step I calculate total sales values of formula:
*
*
Animated demonstration of the solution:
*
*
Good luck
Replacing Selectlist() by multiplication with a dynamic range of values is a great idea, Tahir!