Collect similar values in a field and finding avg of individual similar value

[waleeed_mahmood]

I have a similar table in my data load:

Product Number	MPG	A	B
111aaaa	A5	21	2.2
222bbb	B6	53	6
3c3c3c	A5	56	31
As34s43	9T	54	12
ADSWRW22	B6	432	12
FWE544SD	9T	214	56
ADS540-FD	A5	32	5

So what i want to do is collect like terms in the MPG column and (A-B)/B for each distinct MPG. After finding the Change, i would also like to find individual avg of EACH MPG. so basically divide the change by the number of times an MPG occurs in the original table. 

Calculation Table: (NOTE: Change and AVG(Change) columns don't have to be separated as long as the avg is found)

MPG	Change	Individual AVG(Change)
A5	(21-2.2)/2.2 + (56-31)/31	((21-2.2)/2.2 + (56-31)/31 + (32-5)/5)/3 = 4.91
B6	(53-6)/6 + (432-12)/12	((53-6)/6 + (432-12)/12)/2 = 21.412
9T	(54-12)/12 + (214-56)/56	((54-12)/12 + (214-56)/56)/2 = 3.16

Final Table:

MPG	Actual Change
A5	4.91
B6	21.412
9T	3.16

You help is appreciated.

Please let me know if you require any more information.

[sunny_talwar · Accepted solution]

Try this

Dimension

MPG

Expression

Avg(Aggr(
    (A-B)/B
, [Product Number], [MPG]))

[waleeed_mahmood]

Hi Sunny,

Thank you for your reply. It worked perfectly for my problem. I was wondering if there is a way to convert this into a load script. Since i used your answer in my set expression, i cant use that variable in my load script. is it possible to do the same in a load script?

Thank you.

[waleeed_mahmood]

Hi Sunny,

Could you please explain why you have the product number as one of the parameters in the aggr() function?

Thanks