Where clauses in expressions in pivot tables

[#1]

Hi,

I am currently using a pivot table to show some data, however I am pulling through one column with two data types in it AA and BA I want to be able to show this data in two distinct columns, at the moment I am using an expression:

SUM ([Budget Amount])

This pulls both data types through, what I really want is something like this:

1 expression with:

SUM([Budget Amount]) WHERE [Field A] = 'AA'

and another expression:

SUM([Budget Amount]) WHERE [Field A] = 'BA'

However you can't use where clauses in the expression editor so how do I tell it to only pull data through when the value in another column is X?

Cheers

Ben

[#2 · Accepted solution]

try this one

sum(if([Field A]='BA',[BudgetAmount]))

regards

peter

[Anonymous]

Or Set Analysis:

SUM({$<[Field A]={'BA'}>} [Budget Amount])

[#4]

Thanks guys, that worked a treat!