Filter second table based on multiple filters

[Ashwinyp]

Hi,

I have 2 tables. The first table contains Mgr1,Mgr2,Mgr3,Mgr4 as 4 different columns (these are Manager names) and some other corresponding details . The 2nd table has Employee name and its corresponding Target.

Now I need to keep Mgr1,Mgr2,Mgr3,Mgr4 as filters in my sheet. For the Mgr that is selected by the user, I need to match it with the Employee name from the 2nd table and show the Target value as a KPI.

I have tried 

sum({ <Emp_name= {"$(=Mgr1)"}> +

            <Emp_name= {"$(=Mgr2)"}>  +

            <Emp_name= {"$(=Mgr3)"}>  +

            <Emp_name= {"$(=Mgr4)"}>    }Target)

But this does not give the correct result. Can someone please help?

[petter]

[ TIP: to show any contributor that you like and appreciate their answers please CLICK the like icon (thumbs up) ]

The structure for you first table seems a bit odd. Normally a data table would have a single column with Mgr nam. Having multiple columns with manager names would automatically associate all these managers with each other which seldom makes sense in an analytical setting and makes it much harder to do efficient analysis.

If there is no strong reason for keeping such a structure I would suggest that you have a table 1 like this:

Mgr, MgrAttribute-1 .... MgrAttribute-n

Then you could do something like this:

Sum( {<Emp=Mgr>} Target ) 

If you do have to keep the structure maybe this will work for you:

Sum({ <Emp_name= {'$(=Mgr1)',$(=Mgr2)','$(=Mgr3)','$(=Mgr4)'}>} Target)

[Ashwinyp]

Hi Petter,

Thanks. But the reason that my 1st table is in this format is because I have daily sales at every Mgr hierarchy level like Mgr1 mapped to Mgr2 -> Mgr3 -> Mgr4

And I need it at this level as I have another metric called 'Actual sales' which will sum up the sales depending on the Mgr1/Mg2/Mgr3/Mg4 selected in the filter.

However, the Target table contains all Mgr (Mgr1,Mgr2,Mgr3,Mgr4) names in a single column with their corresponding targets. So I would like to filter this table based on the filter that is selected.

[Ashwinyp]

I'm having a problem using this solution:

Sum({ <Emp_name= {'$(=Mgr1)',$(=Mgr2)','$(=Mgr3)','$(=Mgr4)'}>} Target)

It is not providing me the correct result as the first table is having the Mgr names at a Hierarchical level.

Due to this, if for example, I select Mgr4=ABC from the filter, then the corresponding Mgr1,Mgr2,Mgr3 mapped to him are getting highlighted inside their respective filters (I do not want to remove this functionality as it will be needed for other purposes). So if I use the above code, the target table is summing up the target sales for the selected Mgr4 and also all Mgr1,2 and 3 mapped to him. 

Is the above code supposed to work only for the filter that is selected? Is there any other way that this name can be stored in a temp variable and referenced for summing up in target table?