Forum Discussion
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:
- SUM([percentage_weight]) / CALCULATE(SUM(myTable[weight)];ALL(myTable[weight])
- SUM([percentage_weight]) / SUM(myTable[weight])
- SUM([percentage_weight]) / CALCULATE(SUM(myTable[weight]);ALLSELECTED(myTable[ID]))
- SUM([percentage_weight]) / CALCULATE(SUM(myTable[weight]);myTable[weight] <> BLANK())
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
1 Reply
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