Forum Discussion

sovereignauto's avatar
sovereignauto
Icon for Helper III rankHelper III
3 years ago
Solved

% 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 DaysClaimType for that row.  

 

I had tried using a Table.SelectRows as part of the calulation but kept getting a could not convert type record to table, so any help would be appreciated! 

 

Thank you

 

 

 

 

 

Tips and Tricks
  • m_dekorte's avatar
    m_dekorte
    3 years ago

    Hi sovereignauto 

     

    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!

     

1 Reply

  • m_dekorte's avatar
    m_dekorte
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    Hi sovereignauto 

     

    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!