How to include zero values in calculations

[Anonymous]

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?

[v-danhe-msft · Accepted solution]

Hi @Anonymous ,

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

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

Result:

Regards,

Daniel He