Calculate weighted average from three columns

[TomStaps]

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 me. Thanks in advance! There is a small example below. For these three columns I need to know the weighted average.

Best Regards,

Tom

[Anonymous · Accepted solution]

Hi @TomStaps ,

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

[TomStaps]

Thank you for the help!

[jppv20]

Hi @TomStaps ,

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

[jppv20]

Hi @TomStaps ,

Can you give an example of the expected result?

[TomStaps]

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.