Trouble with the TOTAL function

alexis — Sat, 16 Nov 2024 19:27:56 GMT

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:

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

((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

Re: Trouble with the TOTAL function

bharathadde — Fri, 13 Dec 2019 21:05:23 GMT

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))

topic Re: Trouble with the TOTAL function in App Development

Trouble with the TOTAL function

Re: Trouble with the TOTAL function