locked
PowerPivot Weighted Averages Accounting For Missing Values Marked #N/A RRS feed

  • Question

  • 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

    Right now, I have a few DAX formulas built to create 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 (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.

    What is the best way to create weighted averages that would account for missing values?

    Thanks in advance!

    Tuesday, March 15, 2016 5:20 PM

Answers

All replies

  • Hi,

    This forum is for developers discussing developing issues involve Excel product like Excel automation and customization.

    Since the issue is more relative to PowerPivot, I would like to move it to PowerPivot forum.

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. 

    Thanks for your understanding.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, March 17, 2016 9:30 AM
  • E.tsang, any luck with this?

    Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, July 29, 2016 9:19 PM
  • E.tsang, you can send a Frown to the Power Pivot team if you run into any more troubles. Thanks!

    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Sunday, October 30, 2016 10:17 AM