Sub Totals in Pivot Table

[Anonymous]

Hi All

I have a requirement where I am using expressions for amount and sale as:

Amount:

Num(sum(aggr(avg(MRC11),[Account Number],[Description])) ,'$#,##0.00'),

Sale:

Num(Aggr(SUM(Net),[Account Number],[Description]) ,'€#,##0.00'),

If I am applying above expressions with aggr I am getting null values.Please help me with this.

ExpectedAmount	ActualAmount	ExpectedSale	ActualSale
22	22	12	12
34	34	12	12
34	34	2	2
Total=56	Total=-	Total=26	Total=-

[vinieme12]

why not format it in the Number tab instead of Num()??

[vinieme12]

try

Num(sum(Aggr(SUM(Net),[Account Number],[Description]) ),'€#,##0.00')

[albertovarela]

Hello,

No sure why you have duplicated records for same Account and Description but assuming that Sales and Amounts are the same this might work. See attached app

[Anonymous]

Hi Vineeth

I have multiple currencies so I cannot format in number tab and the formula you gave is not giving expected result.It should give 12+12+2=14 but it is giving 26

[Anonymous]

Hi Alberto

I am not unable to open your attachment as I am using Personal Edition.Data is like that they are not duplicated records

[albertovarela]

Hope this helps

[Anonymous]

Actually this is the problem I need 22+22 as 22 itself that I am getting but if I have 3 sites with sale as 22,22,12 it is giving null value instead of giving 22+12

[albertovarela]

I used the data on your spreadsheet. I think it does what you are asking for...look at the CountryPlace site for Account ABCD1.

[Anonymous]

Sorry for my confusion I am attaching a image according to my requirement