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))))))))
