Dynamic array -select all- slicers

New Contributor

Hello all,


I could really need some help with a formula. See this example.

Basicly I'm trying to make a custom pivot table that has to interact with a couple of slicers. Only problem is that all these slicers also need to have a 'select all' option. I can't figure out how to solve this.


For example:

If I choose customer A, product 1, market all in the slicer; the expected result should be 100 instead of 1200.


Any help would be greatly appreciated!

3 Replies
best response confirmed by PvO123435 (New Contributor)



x,IFERROR(UNIQUE(FILTER(Tabel1[Customer],(IF(L8="All",Tabel1[Customer],L8)=Tabel1[Customer])*(IF(M8="All",Tabel1[Product],M8)=Tabel1[Product])*(IF(N8="All",Tabel1[Market],N8)=Tabel1[Market]))),"No match"),
z,IFERROR(UNIQUE(FILTER(Tabel1,(IF(L8="All",Tabel1[Customer],L8)=Tabel1[Customer])*(IF(M8="All",Tabel1[Product],M8)=Tabel1[Product])*(IF(N8="All",Tabel1[Market],N8)=Tabel1[Market]))),"No match"),
yy, TRANSPOSE( MMULT(  TRANSPOSE( INDEX( z, 0, 4) ), --( TRANSPOSE(x)=INDEX(z,0,1) )) ),
Wow this is great! Trying to figure out what you exactly did, but the solution is perfect. Thanks a lot!

@PvO123435 , you are welcome