Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
TomStaps
Helper I
Helper I

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

 

TomStaps_1-1634215662538.png

 

Best Regards,

Tom

 

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

5 REPLIES 5
v-jayw-msft
Community Support
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Thank you for the help!

jppv20
Solution Sage
Solution Sage

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
Solution Sage
Solution Sage

Hi @TomStaps ,

 

Can you give an example of the expected result?

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. 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.