Forum Discussion
Grouping Columns
I've got a table that organizes like the following:
| Date | Type | A1 | A2 | B1 | B2 | B3 |
| 1/1/2022 | Red | 2 | 3 | 1 | 3 | 3 |
| 1/2/2022 | Blue | 3 | 2 | 2 | 0 | 2 |
| 1/3/2022 | Green | 2 | 3 | 3 | 3 | 1 |
I need to create a table that groups together the A and B columns so it looks like the following:
| Date | Type | A | B |
| 1/1/2022 | Red | 5 | 6 |
| 1/2/2022 | Blue | 5 | 2 |
| 1/3/2022 | Green | 5 | 7 |
How would I do this in DAX?
Thanks!!
6 Replies
Hi Anonymous
Is this a source table or a matrix visual? Are you looking for a measure or a calculated table?Here's PQ:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJS0lEKSk0BkiCWMRAbQmljpVgdkDIjmDKnnNJUqJwRFBuAaYg6Y5g696LU1DwkA43hBsfGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Type = _t, A1 = _t, A2 = _t, B1 = _t, B2 = _t, B3 = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Date", "Type"}, "Attribute", "Value"),
#"Split Column by Position" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByPositions({0, 1}, false), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}, {"Value", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.2"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Date", "Type", "Attribute.1"}, {{"Total", each List.Sum([Value]), type number}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute.1]), "Attribute.1", "Total", List.Sum)
in
#"Pivoted Column"
hi Anonymous
not sure if i fullly get you. you can create a table like this:
SumTable2 = SELECTCOLUMNS( data, "Date", data[Date], "Type", data[Type], "A", [A1]+[A2], "B", [B1]+[B2]+[B3] )i works like this:
