How to divide or direct percentage from sum column to each column value

srehman

Board Regular
Joined
Jan 4, 2020
Messages
140
Office Version
2016
Platform
Windows
Hi Team,

I want to get divide or direct percentage of column A,B,C,D , Dead column by sum. no decimal required with percenatge. only in PQ.

I am doing one by one each column which long process.

Book1
CDEFGHIJ
2IndexSKUABCDDEADSUM
3115163160600006
4262958900600006
5369591300600006
6415131420600006
7526431470600006
8616021240600006
9726439080600006
10816298490600006
11913241130600006
121015120560600006
131164145520600006
141262957530600006
151362953570510006
161413482750510006
171528630060510006
181669595350420006
Sheet1
 

Attachments

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,680
Office Version
2019
Platform
Windows
Here is the Mcode for only adjusting Column A. You will need to do the same for each of the other columns. I saw no point in repeating this exercise since it was for a learning experience exercise and not the actual data.

Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"SKU", Int64.Type}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}, {"DEAD", Int64.Type}, {"SUM", Int64.Type}}),
    #"Inserted Percent Of" = Table.AddColumn(#"Changed Type", "Percent Of", each [A] / [SUM] * 100, type number),
    #"Inserted Round Up" = Table.AddColumn(#"Inserted Percent Of", "Round Up", each Number.RoundUp([Percent Of]), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Round Up",{"Percent Of"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Index", "SKU", "Round Up", "A", "B", "C", "D", "DEAD", "SUM"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"A"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Round Up", "A"}})
in
    #"Renamed Columns"
 

srehman

Board Regular
Joined
Jan 4, 2020
Messages
140
Office Version
2016
Platform
Windows
Thanks Yes i have done same way one by one.
I was looking for short method. I understand sometime no choice.

Thanks for your time. You always helpful.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,680
Office Version
2019
Platform
Windows
Don't know if there is a way to do multiple columns at once. But once the code is done, it can be used over and over again.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,757
try
Rich (BB code):
// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Index", "SKU", "SUM"}, "Attribute", "Value"),
    Prefix = Table.TransformColumns(Unpivot, {{"Attribute", each "% " & _, type text}}),
    PercentOf = Table.AddColumn(Prefix, "Percent Of", each [Value] / [SUM] * 100, type number),
    Round = Table.TransformColumns(PercentOf,{{"Percent Of", each Number.Round(_, 0), type number}}),
    RC = Table.RemoveColumns(Round,{"Value"}),
    Pivot = Table.Pivot(RC, List.Distinct(RC[Attribute]), "Attribute", "Percent Of", List.Sum),
    TSC = Table.SelectColumns(Pivot,{"Index", "SKU", "% A", "% B", "% C", "% D", "% DEAD", "SUM"})
in
    TSC
 

srehman

Board Regular
Joined
Jan 4, 2020
Messages
140
Office Version
2016
Platform
Windows
Oh i Just checked i will give you response time difference it was night here in Melbourne.
Thank again Sandy
 

srehman

Board Regular
Joined
Jan 4, 2020
Messages
140
Office Version
2016
Platform
Windows
Hi Sandy,

I have implemented your approached unbelievable ideas you have.

Thanks a lot.
 

Attachments

Watch MrExcel Video

Forum statistics

Threads
1,102,140
Messages
5,484,976
Members
407,478
Latest member
wsupaul

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top