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

weights	values1	values2
5	3.4	5.8
3	2.1	6.7
1	1.2	7.1
10	1.8	#N/A
50	5	6.6

<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;">
</tbody>

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,