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

Need Help

7 Replies

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

       

       

  • MartynRamsden's avatar
    MartynRamsden
    Solution Sage
    6 years ago

    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's avatar
      michelp
      Frequent Visitor
      6 years ago

      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"

       

      • michelp's avatar
        michelp
        Frequent Visitor
        6 years ago

        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]),