Weighted Average Rate

[shumailh]

I am trying to calculate the weighted average rate for the below sample data in which i am showing only one product as A which has multiple customers having different rates and amount.

Product	Acc	Rate	Amount	Wght Avg Amt	Wght Avg Rate
A	A00001	3.25	26,871.08	87,331.01
A	A00002	3.25	26,871.08	87,331.01
A	A00003	3.25	26,871.08	87,331.01
A	A00004	3.25	26,871.08	87,331.01
A	A00005	1.25	5,321.88	6,652.35
	Total		112,806.20	355,976.39	3.16

Formulas:

1. Wght Avg Amt = Rate * Amount

2. Wght Avg Rate = Sum of Wght Avg Amt / Sum of Amount

Output I am looking for is Productwise Wieghted Average Rate as follows:

Product	Weighted Avg Rate
A	3.16
B	2.45
C	3.12
D	6.50

[CELAMBARASAN · Accepted solution]

Hi,

     Have product as dimension.

     Expression as =Sum(aggr(Avg(Amount*Rate),Acc))/Sum(Amount)

Hope it helps
Celambarasan

[tresB]

Hi,

have a look at the attachment. hope this helps.

Regards,  tresesco

[shumailh]

Thanks Celambarasan Adhimulam.