Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Weighted Average shows wrong sum

Hi there,

I am struggling with computing the weighted average of deviations. I have a range of deviation from -304 to 88 and the count of how many times one deviation occurs. For example: the deviation of -1 occurs 38,380 times, the deviation of 8 only 388 times. Now I want the weighted average of these deviations.

First I calculate the weight of each deviation. Then I multiply the weight and the deviation. For the dviation of -3:

-3 * 0.0614 = -0.1841

After I calculate all theses numbers I need to sum up the last column. The sum shows the total of 88 ( the max deviation of 88 * 100% which is wrong). I tried to calculate the sum with SUMX but its not working. 

Am I approaching the problem wrong? I hope you understand my issue and sorry for bad english.

 

Huge thanks in advance.

Best regards!

Need Help
  • TomasAndersson's avatar
    TomasAndersson
    3 years ago

    Hi!

    Looks to me like SUMX(Table, Table[Deviation] * Table[Weight]) should give your desired result, but maybe not. What result are you getting?

    Is "Deviation + Weight" a calculated column? If you SUM() that in a separate measure SUM(Table[Deviation + Weight]), do you get your expected result then?

    If you are able to share some sample data it might be easier to help you figure it out.

4 Replies

  • TomasAndersson's avatar
    TomasAndersson
    Solution Sage
    3 years ago

    Hi!

    Looks to me like SUMX(Table, Table[Deviation] * Table[Weight]) should give your desired result, but maybe not. What result are you getting?

    Is "Deviation + Weight" a calculated column? If you SUM() that in a separate measure SUM(Table[Deviation + Weight]), do you get your expected result then?

    If you are able to share some sample data it might be easier to help you figure it out.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi,

      thanks for your reply.

      As of now I only have measures. I will try to calculate with calculated columns. If this wont help, I'll try to share sample data. 

      • TomasAndersson's avatar
        TomasAndersson
        Solution Sage
        3 years ago

        Ah, ok. Would below get you the expected result?

        Measure = 
sumx('Table',
    DIVIDE(
        'Table'[Deviation],
        [Count deviation all]
    )
)