Average help

[ajaykumar1]

Hi Everyone,

Greetings of the day !.

I need some help to calculate the average formula with below mentioned output format.

I tried like this ;  avg(aggr(sum(amount),[week]))

Please find the attached sample data.

Input Data :

ID	Week	amount
1	37	5500
1	38	20000
1	45	13000
1	47	24000
1	50	24000
5	40	15000
5	44	17000
10	43	50000
12	46	80000
12	47	70000

Week is my dimension.I taken in Pivot table for to fulfil in horizontal way.

Out put :

ID	Week	37	38	40	43	44	45	46	47	50	Grand Total	Average
1		5500	20000				13000		24000	24000	86500	17300
5				15000		17000					32000	16000
10					50000						50000	50000
12								80000	70000		150000	75000
Total	5500	20000	15000	50000	17000	13000	80000	94000	24000	318500

More Thanks,

Ajay

[swuehl]

I think you may need to add ID to your aggr() dimensions:

avg(aggr(sum(amount),[week], ID))

[sunny_talwar]

This?

Expression: =Avg(Aggr(Sum(amount), ID, Week))

[ajaykumar1]

Thanks for quick reply....But its not giving expected output.

I want average also...Upto grand total i got it.

[Anonymous]

PFA..

[sunny_talwar]

You want average and grand total both?

[ajaykumar1]

Yes....I want Grand total and Average last right side.

Your earlier output is right but i need grand total also...Your average is perfect.

[sunny_talwar · Accepted solution]

This:

Script:

Table:

LOAD ID,

    Week,

    amount

FROM

Avg_Help.xlsx

(ooxml, embedded labels, table is [Data ]);

Dim:

LOAD * Inline [

Dim

1

2

];

Pivot Table

Dimensions:

ID

=Pick(Dim, Week, 'Total')

    Dimension Color Expression: If(Pick(Dim, Week, 'Total') = 'Total', RGB(245,245,245))

    Dimension Text Format: =If(Pick(Dim, Week, 'Total') = 'Total', '<B>')

Expression:

=If(Pick(Dim, Week, 'Total') = 'Total', Sum(DISTINCT Aggr(NODISTINCT Sum(amount), ID)), Avg(Aggr(Sum(amount), ID, Week)))

Background color expression:

If(Pick(Dim, Week, 'Total') = 'Total', RGB(245,245,245))

Credit to Kush141087 for introducing this Pick technique to me.

Best,

Sunny

UPDATE:

Sort Expression for calculated Dimension:

=Pick(Dim, Week, 'Total') with Descending Sort

[settu_periasamy]

Hi,

May be try this..

Add one dummy dimension, and use the SecondaryDimensionality() in your expression..

Like..

=if(SecondaryDimensionality()=1,sum(aggr(sum(amount),ID,Week)),

  if(SecondaryDimensionality()=0,Avg(Aggr(sum(amount),ID,Week)),(sum(amount))))

[ajaykumar1]

Thanks Sunny.Its working fine.

I need to understand the 'Dim'Technique.

Please explain this logic when you get time.

Thanks,

Ajay