Creating subtotal and Grandtotal by grouping

[Anonymous]

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

[v-yingjl · Accepted solution]

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.

[AlexisOlson]

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.

[Anonymous]

Hi,

I have created Group column, but how I can get subtotal and grandtotal?

Thanks,

Amit Kumar

[AlexisOlson]

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.

[Anonymous]

I want to do the same operation starting from row 15. Like this-