Power Query/Power BI: divide each row by total of another column

1
votes

I'm pretty new using queries and I still need to get on the topic properly.. :)

I'm editing a query in Power BI, and I created a new column that shouls show the division of each cell within a column by the total of another column, as %.

I wrote the following: = Table.AddColumn(#"Renamed Columns", "CONTACTED (CVR)", each [CONTACTED]/[LEADS]), but it divide each cell of the column Contacted by each cell of the column Leads.

Is there any way to do it?

Thanks in advance.

Cheers, AS

4
powerbipowerquery

4 Answers

2
votes

In Power Query:

= Table.AddColumn(#"Renamed Columns", "CONTACTED (CVR)", each [CONTACTED]/List.Sum(#"Renamed Columns"[LEADS]))
2
votes

This should do the trick, or get you going in the right direction.

You're looking for the Table.Group function aka Transform->Group By in the GUI. Detailed info here: https://msdn.microsoft.com/en-us/library/mt260774.aspx .

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Leads", Int64.Type}, {"Contacted", Int64.Type}, {"User", type text}}),
  #"Grouped Rows" = Table.Group(#"Changed Type", {"Country"}, {{"Leads_Total", each List.Sum([Leads]), type number}, {"Contacted_Total", each List.Sum([Contacted]), type number}}),
  #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Percent", each [Contacted_Total]/[Leads_Total]),
  #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Leads_Total", "Contacted_Total"}),
  #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Country", "Country2"}}),
  #"Result" = Table.Join( Source , "Country" , #"Renamed Columns" , "Country2" , JoinKind.LeftOuter ),
  #"Removed Columns1" = Table.RemoveColumns(Result,{"Country2"})
in
  #"Removed Columns1"

Hope it helps.

1
votes

I think this is what you are asking; you need to make your question a MWE next time.

Drag the value (my value is called "Place") into the toolbar on the right hand side twice.

Right click the second value and select "Quick calc"

enter image description here

Make sure the "Summerize Value By" is Sum.

then select percent of grand total under "Show Value As".

enter image description here

that should do it.

0
votes

Considering your condition per each country how many contacted out of leads, that'll do:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Types = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Leads", Int64.Type}, {"Contacted", Int64.Type}, {"User", type text}}),
    AddColumn = Table.AddColumn(Types, "Contacted (CVR)", (x) => x[Contacted]/List.Sum(Table.SelectRows(Types, (y)=> x[Country] = y[Country])[Leads]), Percentage.Type)
in
    AddColumn