Conditional Expression with a filter

[#1]

Hello

Can someone please help me with my formula in a pivot table?

= if(Account <> 'GP%', sum(if(Type = 'Actual', Amount)), if (Account = 'GP%',

and this is where I need help.

If Account = GP% then the formula for this is sum(AmountGP)/sum(AmountInv) however it needs to be filtered on Type = 'Actual' in the first place.

As far as I can work out to perform this I need to have the sum outside the brackets that include the Type.  This however gives me the wrong amount though as it is summing together the product of hte formula when I need it to work the other way.

How can I filter on Type = 'Actual' and then perform my formula?

Thankyou in advance,

[vgutkovsky · Accepted solution]

So you're trying to check for the accounts that start with 'GP'? Try this:

if(left(Account,2) <> 'GP',

      sum({<Type={'Actual'}>} Amount),

      sum({<Type={'Actual'}>} AmountGP)/sum({<Type={'Actual'}>} AmountInv)

)

Regards,

Vlad

[#3]

Vlad

Thankyou so much that was exactly what I needed!!!  BRILLIANT!! 

Also sorry it took so long to get back to you - got pulled onto another project.

Thanks again.

Fiona,