Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate weighted average from three columns

Hello all, I'm trying to calculate the weighted average over three columns. Is there a formula that can be used to apply this in Power BI? I can't figure it out myself. Hopefully somebody can help m...
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    There are plenty of blank rows in your sample data. And the average*count is the sum of column, so I used sum() function in the formula directly. Please check the formula.

     

     

    w_avg = 
var count1 = CALCULATE(COUNT('Table A'[Column1]),'Table A'[Column1]<>BLANK())
var count2 = CALCULATE(COUNT('Table A'[Column2]),'Table A'[Column2]<>BLANK())
var count3 = CALCULATE(COUNT('Table A'[Column3]),'Table A'[Column3]<>BLANK())
var sum1 = SUM('Table A'[Column1])
var sum2 = SUM('Table A'[Column2])
var sum3 = SUM('Table A'[Column3])
return
(sum1+sum2+sum3)/(count1+count2+count3)

     

    Result is 4.

     

    Best Regards,

    Jay

jppv20's avatar
jppv20
Solution Sage
4 years ago

Hi Anonymous ,

 

Can you give an example of the expected result?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I tried to descripe it. See below:

     

    1. For all grades per cathegory I made the calcution --> =(SUM per grade per column/selected grade).

     

    2. Add up the outcomes per column.

    Column 1: (three times 3, two times 4, two times 5) --> Total of 7

    Column 2: (five times 3, three times 4, four times 5) --> Total of 12

    Column 3: (two times 3, one time 4, four times 5) --> Total of 7

     

    3. Calculate average per column:

    Column 1: 3.86

    Column 2: 3.92

    Colum 3: 4.29

     

    4. Calculate weighted average from all three columns

    =((7*3.86)+(12*3.92)+(7*4.29))/(7+9+7)=Outcome

    =((add up per cathegory column 1*grade average per cathegory 1)+(add up per cathegory column 2*grade average per cathegory 2)+(add up per cathegory column 3*grade average per cathegory 3)/(add up per cathegory column 1 + 2 + 3)

     

    Hope this makes is clear. If there are questions, please let me know. I hope you can help.