Trouble with the TOTAL function

[alexis]

I am building a pivot table where the measure (NetAmount for example) is of the type:

= (Sum({<[TransactionType] = {'I'}>} Amount)
- (Sum({<[TransactionType] = {'C'}>} Amount)

I also need another measure (lets call it NetAmount(%) ) which represents the part of each row to the total.

If the measure was as simple as Sum(Amount) then the % would be calculated as:

Sum(Amount) / Sum(Total Amount)

However in this case  the Sum part is not so simple and  I am having trouble with the TOTAL part:

The following versions didn't give me the correct results:

a)

((Sum({<[TransactionType] = {'I'}>} Amount)
- (Sum({<[TransactionType] = {'C'}>} Amount))
/
((Sum(Total{<[TransactionType] = {'I'}>} Amount)
- (Sum(Total{<[TransactionType] = {'C'}>} Amount))

b)

((Sum({<[TransactionType] = {'I'}>} Amount)
- (Sum({<[TransactionType] = {'C'}>} Amount))
/
Sum(Total(
((Sum({<[TransactionType] = {'I'}>} Amount)
- (Sum({<[TransactionType] = {'C'}>} Amount))))

Any suggestions as to how I can achieve the desired result?

Thanks in advance

[bharathadde]

Try this and let me know if it works

((Sum({<[TransactionType] = {'I'}>} Amount)
- (Sum({<[TransactionType] = {'C'}>} Amount))
/
((Sum({<[TransactionType] = {'I'}>} Total <TransactionType> Amount)
- (Sum({<[TransactionType] = {'C'}>} Total <TransactionType> Amount))

or may be  (I'm thinking you need group by any particular field)

((Sum({<[TransactionType] = {'I'}>} Amount)
- (Sum({<[TransactionType] = {'C'}>} Amount))
/
((Sum(Total <Groupbytype(ex:Month)> Amount)
- (Sum(Total <Groupbytype(ex:Month)> Amount))