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

Anonymous's avatar
Anonymous
Not applicable
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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you for the help!