Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
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 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

 

sovereignauto_1-1684146949064.png

 

 

 

 

1 ACCEPTED SOLUTION
m_dekorte
Super User
Super User

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.

m_dekorte_0-1684148884100.png

 

Ps. If this helps solve your query please mark this post as Solution, thanks!

 

View solution in original post

1 REPLY 1
m_dekorte
Super User
Super User

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.

m_dekorte_0-1684148884100.png

 

Ps. If this helps solve your query please mark this post as Solution, thanks!

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors