Jan 10 2022 07:38 AM - edited Jan 10 2022 07:48 AM
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!
Jan 10 2022 08:35 AM
SolutionPerhaps
=LET(
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) )) ),
CHOOSE({1,2},x,yy)
)
Jan 12 2022 01:59 AM
Jan 10 2022 08:35 AM
SolutionPerhaps
=LET(
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) )) ),
CHOOSE({1,2},x,yy)
)