Filter Objects on Tab Only (Not Globally)

[#1]

I've got some data that is calculated based on a realtive pool. In the example below, I've got data that includes student name and number of siblings. I want to know what percent of total siblings each student has relative to all students in the class. The tables below demonstrate this concept. As you change your filters, the relative pool size would adjust.

Figure A: Global Filter

Let's say that in my class there are four students: Matthew, Mark, Luke, and John. They have a total of 15 siblings. The data would look like this.

Student	Number of Siblings	Percent Total Siblings
Matthew	4	27% (4/15)
Mark	3	20% (3/15)
Luke	6	40% (6/15)
John	2	13% (2/15)
TOTAL	15	100% (15/15)

Figure B: Filter 2

Now let's say I just want the pool to consist of Matthew and Mark. The data would look like this:

Student	Number of Siblings	Percent Total Siblings
Matthew	4	57% (4/7)
Mark	3	43% (3/7)
TOTAL	7	100% (7/7)

So far I've done all this easily and successfully. My challenge is to create a separate tab that highlights each student individually, relative to the total class. This table would be driven by a list box or variable input where you select which student you want to profile. So say I wanted to just view Matthew. I would select his name in a list box or variable input to get the following table. What I want to do here is only filter the objects on this sheet, not apply a global filter that is going to mess with the relative pool. Does that make sense?

So I pick Matthew from a list... and it basically shows me Figure A but just for Matthew. How would I do this? Is there a way to set a filter that only applies to objects on a certain tab but doesn't actually play into the total global filter? Thank you sincerely for your thoughts and feedback. I would share the document itself but it contains sensitive information so I can only speak using this example for now.

Student	Number of Siblings	Percent Total Siblings
Matthew	4	27% (4/15)

[stephencredmond]

Hi,

I am guessing that your % calculation is something like this:

   Sum([Number of Siblings]) / Sum(TOTAL [Number of Siblings])

So, you could exclude selection on the Student field by including it in a set like this:

   Sum([Number of Siblings]) / Sum({<Student=>} TOTAL [Number of Siblings])

Regards,

Stephen

[#3]

This did help in many instances. However it does not seem to work when I'm using a Sum( TOTAL AGGR  (()) function. Is there a way to make this work with those types of functions as well?

[stephencredmond]

Hi,

You probably need to include an appropriate set in the Sum( AGGR) also.

Regards,

Stephen