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

[Peter_Cammaert]

Dim is a field belonging to a so-called date island. This means that it has no association with any of the other fields or tables in your data model. Using Dim with other fields and expressions in a chart will produce a cartesian product, meaning that both Dim values are artificially combined with everything in your table. The Pick function intercepts certain parts and returns a week number when Dim = 1, and a text value 'Total' when Dim = 2.

This is called a calculated dimension.

Best,

Peter