CODEWITHSUNDEEP
powerbipowerquery
Andrea Spagnuolo
Andrea Spagnuolo

Reputation: 21

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

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

Upvotes: 2

Views: 5991

Answers (4)

Eugene
Eugene

Reputation: 1264

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

Upvotes: 0

Alexander Toptygin
Alexander Toptygin

Reputation: 407

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.

Upvotes: 2

MarcelBeug
MarcelBeug

Reputation: 2967

In Power Query:

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

Upvotes: 3

jpf5046
jpf5046

Reputation: 797

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.

Upvotes: 1

Related Questions