Ignore Filter Condition

[MH376]

I have a following data: 

Store	Department	Item	Sales
A	Office	Pen	200
A	Office	Pencil	300
A	Office	Paper	400
A	Electronics	Printer	500
B	Office	Pen	100
B	Grocery	Tea	200
B	Grocery	Coffee	300
B	Grocery	Bread	300
C	Office	Pencil	350
C	Grocery	Tea	250
C	Toy	Barbie	150

I have Filter on Store, Department, item. When I select Grocery from filter, it should display store that contains grocery and total sales not just grocery sales.

Store	Sales
B	900
C	750

Similarly if I select item Pen it should display only stores that have sales of pen but the sales should include entire sales of all items. 

Store	Sales
B	900
A	1400

How can I achieve this ?

[marksouzacosta]

Hi @MH376,

This is a very unusual request; I would not recommend this approach since it can confuse users. Nevertheless, this is the expression you can use:

Sum({1< Store = {"=Sum(Sales)>0"}>} Sales)

Regards,

Mark Costa

[Vegar]

You can use indirect set analysis for this. I believe this will do it.

Sum({< Store = P(Store), Department, Item>} Sales)