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
<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!
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>
(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!