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

## Sum of a column

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

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

### Re: Sum of a column

How about this:

(See comments in the M code.)

(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"

### Re: Sum of a column

Rudi {L_WROTE}{L_COLON}How about this:

(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

### Re: Sum of a column

Rudi {L_WROTE}{L_COLON}How about this:

(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

### 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)

eg:

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

### Re: Sum of a column

I don't understand your second question?

Sorry!

Sorry!

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

### 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"

### Who is online

Users browsing this forum: No registered users and 0 guests