creating aggregation in total table

[#1]

Hi,

I have to create a total table started from another one which is more detailed, so I'm talking about an aggregation.
My actual table is highlighted in violet in the attachment.
It displays the quantity and the Previous Year Price for each material. Now I have to create the same indicators but at a higher level namely Material_Group.
Being an average, the calculation is made in this way:
First, I have to multiply the quantity by the PY price for each material, then I'll summarize the results and finally I'll divide it by the total quantity. Is in fact the weighted average:
sum(Qty*PY Price)/sum(Qty).
Considering in my table I won't have the detail for material, I think it will be necessary to use some aggregations.
I've tried to do something like this:
(sum(aggr(PY_Price, MAT_MATERIAL_CODE))*
sum(aggr(QUANTITY,MAT_MATERIAL_CODE)))/
sum(QUANTITY)
but I've got an wrong numerator.
Do you have any ideas?

Thank you

[hic]

You should probably use

     Sum(PY_Price*QUANTITY)/Sum(QUANTITY)

and use MAT_MATERIAL_CODE as dimension.

HIC

[#3]

Hi,

I tried but I didn't get any correct results. I'm focusing on getting the numerator value (PY*QTY)

I've attached my application. In the MATERIAL LEVEL block I have all the materials, on the row PY AVG MAT*QTY I calculated the product between PY and QTY.

Now I should do the same in the GROUP LEVEL block, on the row avg*QTY, but I don't get any result.

For example, for January I'm expecting to get the sum of  PY AVG MAT*QTY for all materials. It would be 157126.346.

[#4]

I did it. It was missing the aggregation for month.

[#5]

I wa wrong. It works only for the january e february. For march I got wrong numbers. I don't understand why. It's very strange.

Please, see in the attachment the formula I used.

[#6 · Accepted solution]

The correct formula is

(aggr($(vPYAVGMAT)*sum(TOTAL_QUANTITY), MAT_MATERIAL_CODE, MONTH_MONTH_OF_YEAR))