sum total regardless of selection

[#1]

Hi All,

Cost Centre Sub Cost Centre Amount

10000 10000_XX 500

10000 10000_XY 500

10000 10000_XZ 500

20000 20000_XX 500

20000 20000_XY 500

20000 20000_XZ 500

Given a table like the above I need to add a column that will give me the total of Cost Centre for each line the cost centre appears on so for the above I would have 1500 for each line of 10000.

But there's a catch! I know I could use aggr to do this, however, there are other selections in list boxes external to this straight table that could restrict the list so that e.g. only 10000 sub cost centres XX and XY would appear, I need the column to still show 1500, I've found using aggr the total changes to 1000.

I've tried using set analysis but can't get it to work - this is what I tried:

=sum(total {$<[Cost Centre] = {[Cost Centre}>} Amount)

Please help!

Thanks.

[#2 · Accepted solution]

sum({1} total <[Cost Centre] > Amount)

[#3]

Thanks Richy. That nearly works, it gives me the total regarless of selection but when you select a cost centre it removes the Amounts from other rows but the rows remain? Thanks

[#4]

I found the problem, added a simple if statement to fix it:

=

if(sum(Amount)=0,0,sum({1} total <[Cost Centre]> Amount))

Thanks again.