Forum Discussion
Creating subtotal and Grandtotal by grouping
I have the input like this-
We have three groups- 1.A, B 2. C,D,R 3. S,T,X,G. So subtotal is calculated on each group, like this.
I want to do repeat the same from row 15 and so on. How I can achieve this through DAX or Power queries. I have millions of rows and want a quick solution.
Thanks,
Amit Kumar
Hi Anonymous ,
You can try this query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc89CoAwDEDhu2TuYFut7egPuKuDUHr/axgyPBG6PUL4SGqVRZx4aa7KqhWtNq1gtWtNVid7l1axupk9WsnqQPnLHnlETp88QGfoCO2hZ+ixc3SC9p2jA3JBzsj6ZnsB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Amt = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Amt", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1, Int64.Type)}}), #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Amt","Index"}, {"Amt","Index"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Data",{{"Name", type text}, {"Amt", Int64.Type}, {"Index", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Index", Order.Ascending}, {"Name", Order.Ascending}}), #"Added Custom" = Table.AddColumn( #"Sorted Rows", "SubIndex", each if List.Contains({"A","B"},[Name]) then 1 else if List.Contains({"C","D","R"},[Name]) then 2 else if List.Contains({"S","T","X","G"},[Name]) then 3 else null, Int64.Type ), #"Sorted Rows1" = Table.Sort(#"Added Custom",{{"Index", Order.Ascending}, {"SubIndex", Order.Ascending}, {"Name", Order.Ascending}}) in #"Sorted Rows1"Use a Matrix visual to show the result:
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Add a calculated column for the group.
Group = VAR Name = Table1[Name] RETURN SWITCH ( TRUE (), Name IN { "A", "B" }, 1, Name IN { "C", "D", "R" }, 2, Name IN { "S", "T", "X", "G" }, 3 )Then you can use that column to easily create subtotals.
It's not clear to me how rows 15 and following fit in to what you're trying to do.
You'd use the matrix visual (instead of the table) and set group and name in the rows box.
You'll need another level if you want 15 and following to show up separately.
Hi Anonymous ,
You can try this query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc89CoAwDEDhu2TuYFut7egPuKuDUHr/axgyPBG6PUL4SGqVRZx4aa7KqhWtNq1gtWtNVid7l1axupk9WsnqQPnLHnlETp88QGfoCO2hZ+ixc3SC9p2jA3JBzsj6ZnsB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Amt = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Amt", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1, Int64.Type)}}), #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Amt","Index"}, {"Amt","Index"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Data",{{"Name", type text}, {"Amt", Int64.Type}, {"Index", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Index", Order.Ascending}, {"Name", Order.Ascending}}), #"Added Custom" = Table.AddColumn( #"Sorted Rows", "SubIndex", each if List.Contains({"A","B"},[Name]) then 1 else if List.Contains({"C","D","R"},[Name]) then 2 else if List.Contains({"S","T","X","G"},[Name]) then 3 else null, Int64.Type ), #"Sorted Rows1" = Table.Sort(#"Added Custom",{{"Index", Order.Ascending}, {"SubIndex", Order.Ascending}, {"Name", Order.Ascending}}) in #"Sorted Rows1"Use a Matrix visual to show the result:
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
