## Sum of a column

luca
Posts: 26
Joined: Sun Mar 03, 2019 6:08 am

### Sum of a column

Hi guys,

is possible to add a custom column with a formula which include a line reference to another column divided by the total value of a third column. So for example

A B Calculated column
1 2 1/10 (sum of B column)
2 5 2/10
4 3 4/10

thanks
Luca

PhilC
Posts: 274
Joined: Tue Sep 09, 2014 8:13 am

### Re: Sum of a column

Hi luca,

Do you need a calculated column? What will be your use of this calculation?

Have a read of Matt's post here: https://exceleratorbi.com.au/calculated ... sures-dax/

Work out if you really need a calculated column or if the solution can be made with measures.

Maybe provide some detail around the end measure required and a sample dataset with the data and expected solution (mock up in Excel) and someone should be able to help with the solution.

Cheers
Phil

Rudi
Posts: 16
Joined: Fri Sep 01, 2017 4:33 pm

### Re: Sum of a column

(See comments in the M code.)

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

`let    //Connect to 2 column table source representing values you posted.    //Column 1 titled: "Data 1"; Column 2 titled: Data 2    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],        //Sum up [Data 2] and store value in Step: Total    Total = List.Sum(Source[Data 2]),    //Create new calculated column dividing [Data 1] by the Total Step    #"Create Calc" = Table.AddColumn(Source, "Calc", each [Data 1]/Total)in    #"Create Calc"`

luca
Posts: 26
Joined: Sun Mar 03, 2019 6:08 am

### Re: Sum of a column

(See comments in the M code.)

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

`let    //Connect to 2 column table source representing values you posted.    //Column 1 titled: "Data 1"; Column 2 titled: Data 2    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],        //Sum up [Data 2] and store value in Step: Total    Total = List.Sum(Source[Data 2]),    //Create new calculated column dividing [Data 1] by the Total Step    #"Create Calc" = Table.AddColumn(Source, "Calc", each [Data 1]/Total)in    #"Create Calc"`

Ty Rudi, i'll try your solution. I tried the same thing before posting simply adding a custom column but it doesn't work. I am very bad at coding in M so custom columns are the easiest solution but i dont know why they dont work in this case. Could you pls explain? thanks again
Bye
Luca

luca
Posts: 26
Joined: Sun Mar 03, 2019 6:08 am

### Re: Sum of a column

(See comments in the M code.)

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

`let    //Connect to 2 column table source representing values you posted.    //Column 1 titled: "Data 1"; Column 2 titled: Data 2    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],        //Sum up [Data 2] and store value in Step: Total    Total = List.Sum(Source[Data 2]),    //Create new calculated column dividing [Data 1] by the Total Step    #"Create Calc" = Table.AddColumn(Source, "Calc", each [Data 1]/Total)in    #"Create Calc"`

I Tried the solution and works but how i can insert a column which replicates for each line the value provided by your "total" step? (it is the same thing i asked above in other words).

Moreover if i would adjust your total making it a subtotal of the same column based on the value of another column (a sort of sumif) what i should do?
thanks
Luca

Rudi
Posts: 16
Joined: Fri Sep 01, 2017 4:33 pm

### Re: Sum of a column

You can create a new column based on the Total created by the Total Step.

eg:

= Table.AddColumn(#"Create Calc", "Total Value", each Total)

Rudi
Posts: 16
Joined: Fri Sep 01, 2017 4:33 pm

### Re: Sum of a column

I don't understand your second question?
Sorry!

luca
Posts: 26
Joined: Sun Mar 03, 2019 6:08 am

### Re: Sum of a column

Rudi {L_WROTE}{L_COLON}You can create a new column based on the Total created by the Total Step.

eg:

= Table.AddColumn(#"Create Calc", "Total Value", each Total)

Thanks Rudi.
The second question was about creating a "total with a filter" (so the same formula you suggested for the total but it has to include only some rows (based on another column value).

So for example

A 1 2 1/7 (sum of C column with A in the first column)
A 2 5 2/7
B 4 3 4/6 (sum of C column with B in the first column)
B 2 3 2/6

thanks
Luca

Rudi
Posts: 16
Joined: Fri Sep 01, 2017 4:33 pm

### Re: Sum of a column

Like this:

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

`let    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],    Total = List.Sum(Source[Data 3]),    #"Grouped Rows" = Table.Group(Source, {"Data 1"}, {{"Group Sum", each List.Sum([Data 3]), type number}, {"Data", each _, type table [Data 1=text, Data 2=number, Data 3=number]}}),    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Data 2", "Data 3"}, {"Data 2", "Data 3"}),    #"Added Custom" = Table.AddColumn(#"Expanded Data", "Calc Group", each [Data 2]/[Group Sum]),    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Calc Total", each [Data 2]/Total),    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Group Sum"})in    #"Removed Columns"` 