Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))