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 ,

Total of Column2 is 12 I assume?

 

Try using this measure:

Weighted Average =
var Count1 = COUNT('Table'[Communication Cooking Appliances])
var Count2 = COUNT('Table'[Communication Sanitation])
var Count3 = COUNT('Table'[Communication Refrigeration])
var Average1 = AVERAGE('Table'[Communication Cooking Appliances])
var Average2 = AVERAGE('Table'[Communication Sanitation])
var Average3 = AVERAGE('Table'[Communication Refrigeration])
Return
((Count1*Average1)+(Count2*Average2)+(Count3*Average3))/(Count1+Count2+Count3)
 
Jori
 
If I answered your question, please mark it as a solution to help other members find it more quickly.

Connect on Linkedin