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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

How to include zero values in calculations

Hi, I'm trying to calculate a weighed average in Power BI. To calculate the weighed average I require to divide a number through the SUM of weight. The table has some 0 values on the amounts and so with the calculations, the weights of the 0 values are not included in the SUM of weight.

 

Lets say I have the following table: myTable:

 

ID      Weight      Score    Percentage_score    percentage_weight
A       2           80       0.8                 1.6
B       3           0        0                   0
C       1           76       0.76                1.52
D       2           55       0.55                1.10
E       3           0        0                   0
F       2           67       0.67                1.34

I want to calculate the weighed average of A t/m F. So after calculating the percentage of Score in the column Percentage_score I calculate the percentage * weight in percentage_weight.

 

The final step is to calculate: SUM(percentage_weight ) / SUM(Weight)

 

everytime I try to do this last step, the formula does not count the weights where it contains score 0. My SUM(weight) = 7 instead of 13. "B" and "E" are not included in the calculation, which results in the wrong output.

 

I have tried the following DAX statements:

  1. SUM([percentage_weight]) / CALCULATE(SUM(myTable[weight)];ALL(myTable[weight])
  2. SUM([percentage_weight]) / SUM(myTable[weight])
  3. SUM([percentage_weight]) / CALCULATE(SUM(myTable[weight]);ALLSELECTED(myTable[ID]))
  4. SUM([percentage_weight]) / CALCULATE(SUM(myTable[weight]);myTable[weight] <> BLANK())
 I cannot find a way to include the 0 values in my weight calculations, so my question is How can I include the weights values in my calculations where the score is 0?
1 ACCEPTED SOLUTION
v-danhe-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous ,

Based on my test, you could refer to below formula:

CALCULATE(SUM('Table1'[percentage_weight]),FILTER('Table1','Table1'[Percentage_score]<>0))

Result:

1.PNG

 

Regards,

Daniel He

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

View solution in original post

1 REPLY 1
v-danhe-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous ,

Based on my test, you could refer to below formula:

CALCULATE(SUM('Table1'[percentage_weight]),FILTER('Table1','Table1'[Percentage_score]<>0))

Result:

1.PNG

 

Regards,

Daniel He

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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