Forum Discussion
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.
Best Regards,
Tom
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
5 Replies
Hi Anonymous ,
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.
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)JoriIf I answered your question, please mark it as a solution to help other members find it more quickly.
Connect on LinkedinHi 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
