Using 'implied' filters in set analysis

[timothywhite1830]

I have a dataset that looks like this:

Fruit	State	Qty
Apple	VA	50
Pear	FL	25
Grape	FL	15
Apple	TX	40
Pear	TX	20
Grape	FL	45

If I select 'Apple' as the fruit, I arrive at this selection set:

Fruit	State	Qty
Apple	VA	50
Apple	TX	40

I would like to conduct analysis on what I would call the 'implied' filter of states (being VA and TX) without having to explicitly select VA and TX.

Essentially, I would like to sum the values from this dataset:

Fruit	State	Qty
Apple	VA	50
Apple	TX	40
Pear	TX	20

Is there a way to use set analysis to pick up the fact that by selecting 'Apple', I now only have VA and TX in the dataset and then create a sum to include all fruit from those two states?

My thanks for your help.

[PrashantSangle · Accepted solution]

Use below 

Chart -- Table

Dim - State, Fruit

Expression - Sum(Qty)+Sum({<Fruit=,State=p(State)>}Qty)

Regards,

Prashant Sangle

[Chanty4u · Accepted solution]

Try this 

SUM({< State = P(State) >} Qty)