Weighted Averages in PivotTables, Excluding Values Not Available

[etsang]

Hi, I'm just starting to use PowerPivot and would like to know how to account for values not available when calculating a weighted average.
My data is structured similar to the below
[TABLE="width: 192"]
<colgroup><col span="3" width="64" style="width: 48pt;"></colgroup><tbody style="border: 0px; font-weight: inherit; font-style: inherit; font-family: inherit; margin: 0px; outline: 0px; padding: 0px;">[TR]
[TD="width: 64"]weights[/TD]
[TD="width: 64"]values1[/TD]
[TD="width: 64"]values2[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]3.4[/TD]
[TD="align: right"]5.8[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2.1[/TD]
[TD="align: right"]6.7[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1.2[/TD]
[TD="align: right"]7.1[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]1.8[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6.6[/TD]
[/TR]
</tbody>[/TABLE]
Right now, I have a few DAX formulas built to create weighted average fields for my pivot table.
(1) TotalWeights = sum([weights])
(2) WtdAvgValues1 = sumx(Table1, [values1] * [weights]) / [TotalWeights]
(2) WtdAvgValues2 = sumx(Table1, [values2] * [weights]) / [TotalWeights]

What I'd like PowerPivot to do is exclude the #N/A in value2 when calculating the weighted average (i.e. excluding the #N/A value from the sumproduct numerator and removing the "10" from the sum of the weights).
But of course, PowerPivot is unable to read the column with the #N/A.
Anyone have suggestions on what is the best way to create weighted averages that would account for missing values?
Thanks in advance!

 

[anvg]

Hi
I am sorry but I was not able to get your mistake. In my file Zippyshare.com - test.xlsx a pivot table has an equal result with "handle" calculated one.
Regards,