Sum of a column

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

Sum of a column

Postby luca » Wed May 15, 2019 8:21 am

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

Postby PhilC » Wed May 15, 2019 9:04 am

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

Postby Rudi » Wed May 15, 2019 5:05 pm

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"

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

Re: Sum of a column

Postby luca » Thu May 16, 2019 1:05 am

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

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

Re: Sum of a column

Postby luca » Thu May 16, 2019 1:15 am

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

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

Re: Sum of a column

Postby Rudi » Thu May 16, 2019 10:26 pm

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

Postby Rudi » Thu May 16, 2019 10:27 pm

I don't understand your second question?
Sorry!

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

Re: Sum of a column

Postby luca » Fri May 17, 2019 5:57 pm

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

Postby Rudi » Mon May 20, 2019 5:06 pm

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"


Return to “Power Query”

Who is online

Users browsing this forum: Bing [Bot] and 3 guests

cron