Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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.
Hi,
I have created Group column, but how I can get subtotal and grandtotal?
Thanks,
Amit Kumar
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.
I want to do the same operation starting from row 15. Like this-
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
45 | |
19 | |
13 | |
11 |