• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power Query: % of total

Lance Pretorius

New Member
In Power Query how do you add a custom column that displays row total as % of column total?
See Below

Thanks in advance

IndexNameTotalColumn to be addedColumn to be added formula
1A100
4.76%​
=$D4/SUM($D$4:$D$9)
2B200
9.52%​
3A300
14.29%​
4B400
19.05%​
5A500
23.81%​
6B600
28.57%​
 

GraH - Guido

Well-Known Member
If your table would be in Excel

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Name", type text}, {"Total", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Sum(Table.Column(#"Changed Type","Total"))),
    #"Inserted Percent Of" = Table.AddColumn(#"Added Custom", "Percent Of", each [Total] / [Custom] * 100, type number)
in
    #"Inserted Percent Of"
Note the step Percent Of is doable via the ribbon:
Select column total then column custom then apply as in screenshot
59946
 

Attachments

Chihiro

Excel Ninja
This sort of calculation is best done in DAX. It's more efficient.

However, there are many ways to do this. Using GUI as GraH showed above for one.

Using calculated column like below.
Code:
=[Total]/List.Sum(Source[Total])
 
Top