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

[Anil_Babu_Samineni]

May be use DISTINCT Keyword for this

[Anonymous]

Can you please explain where can I use it in my expression

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

[vinieme12]

Like this?? vanivadlamudi

<<Modifications>>

SALES

=num(if(rowno()=0,Sum(aggr(avg(Sale),AccountNumber,Region)),sum(aggr(avg(Sale),AccountNumber,Desc))),'€ #,##0.00')

AMOUNT

=num(if(rowno()=0,Sum(aggr(avg(Amount),AccountNumber,Region)),sum(aggr(avg(Amount),AccountNumber,Desc))),'€ #,##0.00')

[#5]

try this

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

[Anonymous]

It was also not working

[Anil_Babu_Samineni · Accepted solution]

Try simple Sum(DISTINCT Net)

And then check partial sum from presentation and then show me the image

[sasikanth]

HI,

Try below Expression,

Num(Sum(AGGR(DISTINCT Sum(Net),[Account Number],Region,Country,City)),'€#,##0.00')

[Anonymous]

Thank you Anil. It worked

but I used it like

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

[sasikanth]

I don`t think simple distinct SUM will work if you have same net value for another combination. ( Region, Country , City, Account Number ), do check once .

Region='SOUTH' county='India', city='Delhi', AccNo=1011 , Net =100

Region='SOUTH' county='Aus',  city='Sidny', AccNo=222 , Net =100

in this case SUM( distinct Net ) =100 instead of 200  ( as per my understanding)

[Anonymous]

Ya Sasi

thats why I used AGGR function and I have mentioned it in my post