Weighted average price of a product per day in Pivot Table

1
votes

I am having issues translating the following formula to a pivot table; either through a regular pivot table, or through DAX and powerpivot.

=SUMPRODUCT((C$2:C$11)*(D$2:D$11)*(A$2:A$11=A2)*(B$2:B$11=B2))/SUMIFS(D$2:D$11,A$2:A$11,A2,B$2:B$11,B2)

The background is, I have a number of products that appear on an e-commerce site, and I need to find out their price per day. However, these prices change daily, based on things like promo codes, visitor location etc. Therefore, I need their weighted price based on the number of visitors that saw a particular price.

Table

Can anyone help with this translation, or alternatively, offer a better way to approach this problem?

PS- I need it in a pivot table due to the volume of data. At 250,000 rows, standard Excel cannot handle this formula.

1
exceldax

1 Answers

2
votes

The following is in Excel 2010 sans Powerpivot. However, the general approach should work:

WeightedPrice

Explanation:

I added a column that multiplies the Prices and Visits. The pivot table uses Dates, then Product SKU as the row labels. Then I added a calculated field that divides the Price*Visits by the Visits.