Weighted average

[norvegia]

Hi, hope someone can help me out!

I have three tables. Weeks, volume and prices. Volume and prices are linked to weeks. There are many items in both the volume and price table per week (volume 2, volume3) The following tables are just for illustration.

Weeks
[TABLE="width: 100"]
<tbody>[TR]
[TD]Week[/TD]
[/TR]
[TR]
[TD]201401[/TD]
[/TR]
[TR]
[TD]201402[/TD]
[/TR]
[TR]
[TD]201403[/TD]
[/TR]
[TR]
[TD]201404[/TD]
[/TR]
[TR]
[TD]201405[/TD]
[/TR]
</tbody>[/TABLE]

Volume
[TABLE="width: 100"]
<tbody>[TR]
[TD]Week[/TD]
[TD]Volume 1 [/TD]
[TD]Volume 2 [/TD]
[/TR]
[TR]
[TD]201401[/TD]
[TD]450[/TD]
[TD]350[/TD]
[/TR]
[TR]
[TD]201402[/TD]
[TD]320[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]201403[/TD]
[TD]250[/TD]
[TD]140[/TD]
[/TR]
[TR]
[TD]201404[/TD]
[TD]140[/TD]
[TD]260[/TD]
[/TR]
[TR]
[TD]201405
[/TD]
[TD]130[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]

Prices
[TABLE="width: 100"]
<tbody>[TR]
[TD]Week[/TD]
[TD]Price 1 [/TD]
[TD]Price 2[/TD]
[/TR]
[TR]
[TD]201401[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]201402[/TD]
[TD]3,5[/TD]
[TD]3,1[/TD]
[/TR]
[TR]
[TD]201403[/TD]
[TD]4[/TD]
[TD]4,1[/TD]
[/TR]
[TR]
[TD]201404[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]201405
[/TD]
[TD]3[/TD]
[TD]4,8[/TD]
[/TR]
</tbody>[/TABLE]

I want to have the weighted average price for the weeks of my choosing. How can I do that? In normal Excel one would use the SUMPRODUCT to achieve this, but that is not possible in Power Pivot.

I have tried the SUM function, but it doesn't work if I choose more than one week.

Thanks!

 

[sheetspread]

Sumx()

 

[norvegia]

Thanks for your reply. But how can I use SUMX() to solve this?

 

[norvegia]

I have found this from powerpivotpro, but I can not make it work because I have more than one table... Weighted Averages: Another Use of SUMX() « PowerPivotPro

 

[sheetspread]

So join the tables first (select the table, design, create relationship)