Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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
Solved! Go to 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
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
Thank you for the help!
Hi @TomStaps ,
Total of Column2 is 12 I assume?
Try using this measure:
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
32 | |
27 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
38 | |
26 | |
24 | |
20 |