Forum Discussion
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...
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
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"
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]),Hi michelp
Your M code looks fine.Are you able to share your pbix so we can investigate further?
Best regards,
Martyn