Ignore Dimension-Filter for If Statement in Pivot Table

[Oliver_F]

Hey there,

I have an really frustrating problem and hope you might be able to help me.

The following example does not make much sense, but is a downsized / abstract version of my problem.

Table:

load * inline

[Dimension1,Dimension2, Value

A,XX,1

B,YY,2

C,ZZ,3];

Now I create a new sheet with

• Pivot-Table
  • Row: Dimension1
  • Colum: Dimension2
  • Expression: if(Dimension2 = 'ZZ', 15, Sum({1}Value))
• Filter
  • Dimension1

This works great if there is no filter set.

But is not working anymore as soon as i use a filter.

I want to have that table completely independent from any filters that are used.

Do you have any idea of how to achieve this?

Thanks very much for your help!

[sunny_talwar]

Try this for color

if(Only({1}Dimension2)='XX', rgb(255,0,0),rgb(0,0,255))

[agigliotti]

you can achieve it using a color by expression as below:

sum( {1} if( Dimension2='XX', rgb(255,0,0), rgb(0,0,255) ) )

and ticking "The expression is a color code".

[Oliver_F]

Thanks Andrea. This  was really a mess. Had to add some minor adjustments to fit my needs, but you defineteley solved this!

THANK YOU VERY MUCH!