Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Find % of Total

Here is my data columns, i need to find the % for each individual, (so for each name total the amount then take individual values and divide by total) but I need the Name column and the Ordered2023 c...
Need Help
  • slorin's avatar
    slorin
    3 years ago

    Hi,

    A solution with Group and Expand. 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsIzALCcgyxTOsoCzLMEsZ7gOZ7gOEMtEKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Names = _t, Count = _t]),
    Type = Table.TransformColumnTypes(Source,{{"Count", type number}}),
    Group = Table.Group(Type, {"Names"}, {{"Sum", each List.Sum([Count])}, {"Count", each [Count]}}),
    Expand = Table.ExpandListColumn(Group, "Count"),
    Percentage = Table.AddColumn(Expand, "%", each [Count] / [Sum], Percentage.Type)
    in
    Percentage

    or with ExpandTableColumn

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsIzALCcgyxTOsoCzLMEsZ7gOZ7gOEMtEKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Names = _t, Count = _t]),
    Type = Table.TransformColumnTypes(Source,{{"Count", type number}}),
    Group = Table.Group(Type, {"Names"}, {{"Sum", each List.Sum([Count])}, {"Data", each _}}),
    Expand = Table.ExpandTableColumn(Group, "Data", {"Count"}, {"Count"}),
    Percentage = Table.AddColumn(Expand, "%", each [Count] / [Sum], Percentage.Type)
    in
    Percentage

    Stéphane 

slorin's avatar
slorin
Icon for Super User rankSuper User
3 years ago

Hi,

A solution with Group and Expand. 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsIzALCcgyxTOsoCzLMEsZ7gOZ7gOEMtEKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Names = _t, Count = _t]),
Type = Table.TransformColumnTypes(Source,{{"Count", type number}}),
Group = Table.Group(Type, {"Names"}, {{"Sum", each List.Sum([Count])}, {"Count", each [Count]}}),
Expand = Table.ExpandListColumn(Group, "Count"),
Percentage = Table.AddColumn(Expand, "%", each [Count] / [Sum], Percentage.Type)
in
Percentage

or with ExpandTableColumn

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsIzALCcgyxTOsoCzLMEsZ7gOZ7gOEMtEKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Names = _t, Count = _t]),
Type = Table.TransformColumnTypes(Source,{{"Count", type number}}),
Group = Table.Group(Type, {"Names"}, {{"Sum", each List.Sum([Count])}, {"Data", each _}}),
Expand = Table.ExpandTableColumn(Group, "Data", {"Count"}, {"Count"}),
Percentage = Table.AddColumn(Expand, "%", each [Count] / [Sum], Percentage.Type)
in
Percentage

Stéphane