Weighted Averages in PowerView

Question

My objective is to create a weighted average of price where the weights depend on the value of size per category, using the PowerView functionality in Excel 2013. For instance the following table:

Table

Category  Price    Size
1         10        5
1         20       15
2         30        3
2         50        8
3          5       10
3         33       10

So, for category 1: (10*5+20*15)/20=17.5 and present this in a table:

Category   weighted avg price
 1         17.5
 2        ....

Instead of the unweighted averages which the standard functionality of PowerView produces. Is this possible?

Answer 1

This might not be any new information for you, but you could create two new columns. Column D that is just B x C. And then a column E that is

=sumif($A$1:$A$6,A1,$D$1:$D$6)/sumif($A$1:$A$6,A1,$C$1:$C$6)

and that will give you the weighted average for each category.