0 Replies Latest reply: Apr 17, 2015 4:04 PM by Allison Bruno Matos Baldan

# Calculating values based on selection

Hello,

So, when I select something on Filter01, a set of values are also selected in Filter02. I have a table where a see all values that are on Filter02 in list. Looks something like this:

Table

Filter02 | Area | Filter01 Productivity  | Filter02 Productivity

Name1     30                                                 60,2

Name1     33                                                 34,2

Name1     43                                                 40,0

Name2     21                                                 55,3

Name2     22                                                 71,7

So, for example: the Name1 is correspondent of Filter01, and he has productivity on many areas like above, he had productivity on area 30, 33 and 43. What I need is to calculate the Filter01 productivity in the same areas that Name1, Name2, etc... (those who got selected on Filter02 when I select something on my Filter01).

Basically, if Name1 had productivity in areas 1, 10, 11, 23, 35, I need to show the productivity of Filter01 on those areas too.

I have a code working, but I'm calculating each area separated and since I have 100+ areas, that's not viable.

Here what I have working, but as you guys can see, each area have one if, so the code will be pretty long.

If you guys can help me simplificate this code I would apprecciate.

if(Filter02 <> Filter01,

if(Avg(Productivity) > 0,

if(Only(Area) = 21, Avg(TOTAL {<Filter02={'\$(=Only(Filter01))'}, Area = {21}>} Productivity),

if(Only(Area) = 22, Avg(TOTAL {<Filter02={'\$(=Only(Filter01))'}, Area = {22}>} Productivity),

if(Only(Area) = 30, Avg(TOTAL {<Filter02={'\$(=Only(Filter01))'}, Area = {30}>} Productivity),

if(Only(Area) = 33, Avg(TOTAL {<Filter02={'\$(=Only(Filter01))'}, Area = {33}>} Productivity,

if(Only(Area) = 43, Avg(TOTAL {<Filter02={'\$(=Only(Filter01))'}, Area = {43}>} Productivity))))))))

Regards

Allison BMB