Forum Discussion
sovereignauto
Helper III
Helper III% of Total for category
Good Morning, I have a table to data that i need to see the % of Total for column [Count] but i want the List.Sum part to olny sum where the [DaysclaimType] column is eq to the value in DaysClai...
Add a Group By step to obtain totals
let // Add a Group By step to obtain totals for each DaysClaimType GroupedRows = Table.Buffer( Table.Group(ChType, {"DaysClaimType"}, {{"Total", each List.Sum([Count]), type nullable number}})), Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8gtKLUjMLHL2CFLSUTI0VYrVQYj5e4LEDFHEwOqMjDDFDLGJmWCaZ0nQOIi1BkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DaysClaimType = _t, Count = _t]), ChType = Table.TransformColumnTypes(Source,{{"Count", Int64.Type}}), AddPctTotal = Table.AddColumn( ChType, "% of Total", each [Count] / Table.SelectRows( GroupedRows, (x)=> [DaysClaimType] = x[DaysClaimType] ){0}?[Total]?, Percentage.Type ) in AddPctTotalYou can refer to that GroupedRows table to select the type.
Ps. If this helps solve your query please mark this post as Solution, thanks!
m_dekorte
Resident Rockstar
Resident Rockstar
Add a Group By step to obtain totals
let
// Add a Group By step to obtain totals for each DaysClaimType
GroupedRows = Table.Buffer( Table.Group(ChType, {"DaysClaimType"}, {{"Total", each List.Sum([Count]), type nullable number}})),
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8gtKLUjMLHL2CFLSUTI0VYrVQYj5e4LEDFHEwOqMjDDFDLGJmWCaZ0nQOIi1BkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DaysClaimType = _t, Count = _t]),
ChType = Table.TransformColumnTypes(Source,{{"Count", Int64.Type}}),
AddPctTotal = Table.AddColumn( ChType, "% of Total", each [Count] / Table.SelectRows( GroupedRows, (x)=> [DaysClaimType] = x[DaysClaimType] ){0}?[Total]?, Percentage.Type )
in
AddPctTotal
You can refer to that GroupedRows table to select the type.
Ps. If this helps solve your query please mark this post as Solution, thanks!