Calculated column sum is null

[michelp]

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 this formula:

=[BL1 Sales Amount] + [BL2 Sales Amount] + [BL3 Sales Amount] + [BL4 Sales Amount] + [BL5 Sales Amount]

Now an extra column is added:

- BL6 Sales Amount

And I have adjusted the formula to:

=[BL1 Sales Amount] + [BL2 Sales Amount] + [BL3 Sales Amount] + [BL4 Sales Amount] + [BL5 Sales Amount] + [BL6 Sales Amount]

However, now the sum is always null... I can't find the reason why. 

Before:

After:

Any suggestions how to troubleshoot this?

Thanks!!

[v-diye-msft · Accepted solution]

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 

[v-diye-msft · Accepted solution]

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 

[MartynRamsden]

Hi @michelp 

I'd start by checking the preceding steps. You may be doing something to the other Sales Amount columns which you also need to do to the 'BL6 Sales Amount' column (changing the data type, for example).

Also, the order in which you apply the steps in the Query Editor can change the result significantly.

If nothing is dependant on the 'Total Sales Amount' column, can you move this to be the last step?

Best regards,

Martyn

[michelp]

Hi,

The calculation is already at the back. I need to calculate a sum for each line. So not for all lines in total. So for example for line 27, I need to know the sum for all [BLx Sales Amount] columns on that line.

this is my code:

let
    Source = Json.Document(xx),
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{2}[Value],
    #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"ForecastId", "Naam", "Scoringskans", "Forecast_open_status", "Verwacht_bedrag_basisvaluta", "Verantwoordelijke", "Project", "Omschrijving_2", "Business_Line_1_-_Omschrijving", "BL1_Aankoopprijs", "BL1_Verkoopprijs", "Business_Line_2_-_Omschrijving", "BL2_Aankoopprijs", "BL2_Verkoopprijs", "Business_Line_3_-_Omschrijving", "BL3_Aankoopprijs", "BL_3_Verkoopprijs", "Business_Line_4_-_Omschrijving", "BL4_Aankoopprijs", "BL4_Verkooppijs", "Business_Line_5-_Omschrijving", "BL5_Aankoopprijs", "BL5_Verkoopprijs", "Business_Line_6_-_Omschrijving", "BL6_Aankoopprijs", "BL6_Verkoopprijs", "Einddatum", "Forecastgroep"}, {"ForecastId", "Naam", "Scoringskans", "Forecast_open_status", "Verwacht_bedrag_basisvaluta", "Verantwoordelijke", "Project", "Omschrijving_2", "Business_Line_1_-_Omschrijving", "BL1_Aankoopprijs", "BL1_Verkoopprijs", "Business_Line_2_-_Omschrijving", "BL2_Aankoopprijs", "BL2_Verkoopprijs", "Business_Line_3_-_Omschrijving", "BL3_Aankoopprijs", "BL_3_Verkoopprijs", "Business_Line_4_-_Omschrijving", "BL4_Aankoopprijs", "BL4_Verkooppijs", "Business_Line_5-_Omschrijving", "BL5_Aankoopprijs", "BL5_Verkoopprijs", "Business_Line_6_-_Omschrijving", "BL6_Aankoopprijs", "BL6_Verkoopprijs", "Einddatum", "Forecastgroep"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"Verwacht_bedrag_basisvaluta", type number}, {"BL1_Aankoopprijs", type number}, {"BL1_Verkoopprijs", type number}, {"BL2_Aankoopprijs", type number}, {"BL2_Verkoopprijs", type number}, {"BL3_Aankoopprijs", type number}, {"BL_3_Verkoopprijs", type number}, {"BL4_Aankoopprijs", type number}, {"BL4_Verkooppijs", type number}, {"BL5_Aankoopprijs", type number}, {"BL5_Verkoopprijs", type number}, {"BL6_Aankoopprijs", type number}, {"BL6_Verkoopprijs", type number}, {"Einddatum", type datetime}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Naam", "Customer"}, {"Scoringskans", "Scoring Opportunity"}, {"Forecast_open_status", "Status"}, {"Verwacht_bedrag_basisvaluta", "Expected Amount"}, {"Verantwoordelijke", "Salesperson"}, {"Project", "Project ID"}, {"Omschrijving_2", "Description"}, {"Business_Line_1_-_Omschrijving", "BL1 Description"}, {"Business_Line_2_-_Omschrijving", "BL2 Description"}, {"Business_Line_3_-_Omschrijving", "BL3 Description"}, {"Business_Line_4_-_Omschrijving", "BL4 Description"}, {"Business_Line_6_-_Omschrijving", "BL6 Description"}, {"Business_Line_5-_Omschrijving", "BL5 Description"}, {"BL2_Aankoopprijs", "BL2 Purchase Amount"}, {"BL1_Aankoopprijs", "BL1 Purchase Amount"}, {"BL3_Aankoopprijs", "BL3 Purchase Amount"}, {"BL4_Aankoopprijs", "BL4 Purchase Amount"},{"BL5_Aankoopprijs", "BL5 Purchase Amount"}, {"BL5_Verkoopprijs", "BL5 Sales Amount"}, {"BL6_Aankoopprijs", "BL6 Purchase Amount"}, {"BL6_Verkoopprijs", "BL6 Sales Amount"}, {"BL4_Verkooppijs", "BL4 Sales Amount"}, {"BL_3_Verkoopprijs", "BL3 Sales Amount"}, {"BL2_Verkoopprijs", "BL2 Sales Amount"}, {"BL1_Verkoopprijs", "BL1 Sales Amount"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Total Sales Amount", each [BL1 Sales Amount] + [BL2 Sales Amount] + [BL3 Sales Amount] + [BL4 Sales Amount] + [BL5 Sales Amount]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Total Sales Amount", type number}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Total Purchase Amount", each [BL1 Purchase Amount] + [BL2 Purchase Amount] + [BL3 Purchase Amount] + [BL4 Purchase Amount] + [BL5 Purchase Amount]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Total Purchase Amount", type number}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type2", "Total Margin Amount", each [Total Sales Amount] - [Total Purchase Amount]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"Total Margin Amount", type number}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type3", "Total Margin Percentage", each [Total Margin Amount] / [Total Sales Amount]),
    #"Changed Type4" = Table.TransformColumnTypes(#"Added Custom3",{{"Total Margin Percentage", Percentage.Type}})
in
    #"Changed Type4"

[MartynRamsden]

Hi @michelp 

Your M code looks fine.

Are you able to share your pbix so we can investigate further?

Best regards,

Martyn

[michelp]

So to add the column, I change 

    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Total Sales Amount", each [BL1 Sales Amount] + [BL2 Sales Amount] + [BL3 Sales Amount] + [BL4 Sales Amount] + [BL5 Sales Amount]),

to 

    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Total Sales Amount", each [BL1 Sales Amount] + [BL2 Sales Amount] + [BL3 Sales Amount] + [BL4 Sales Amount] + [BL5 Sales Amount] + [BL6 Sales Amount]),

[Tahreem24]

@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]

@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!