Forum Discussion

michelp's avatar
michelp
Frequent Visitor
6 years ago
Solved

Calculated column sum is null

  Hi Folks,   I have a table with 5 decimal columns: - BL1 Sales Amount - BL2 Sales Amount - BL3 Sales Amount - BL4 Sales Amount - BL5 Sales Amount   I Have created a calculated column with...
Need Help
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    Hi michelp 

     

    The + operator is returning a null because the [BL5 Sales Amount] field is null ( number + null = null). This behavior is standard for all operators within PQ (+,-,/,*) and sql. You can override this behavior with List.Sum. List.Sum ignores nulls by definition. 

     

    https://docs.microsoft.com/en-us/powerquery-m/list-sum 

     

     

Tahreem24's avatar
Tahreem24
Super User
6 years ago

michelp ,

 

Create measure to get the sum.

Value6  = Sum(Sheet7[Value1])+sum(Sheet7[Value2])+sum(Sheet7[Value3])+sum(Sheet7[Value4])+sum(Sheet7[Value5])
 
Don't forget to hit Thumbs up and accept this as a solution if you find it helpful! 
Tahreem24's avatar
Tahreem24
Super User
6 years ago

michelp ,

 

I have one doubt why you wanna sum [BL6 Sales Amount] again with all 5 sales amount?

Instead of using your below statement use the NEw Total Sum.

New Total Sum = Value6+Value6

 

This also gives you same result like [BL1 Sales Amount] + [BL2 Sales Amount] + [BL3 Sales Amount] + [BL4 Sales Amount] + [BL5 Sales Amount] + [BL6 Sales Amount]

 

Don't forget to hit Thumbs up and accept this as a solution if you find it helpful!