Grouping Columns

[Anonymous]

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

[FreemanZ]

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:

[Anonymous]

Are these columns name (Ax, Bx) going to be fixed or dynamic?

Why not Power Query?

[Anonymous]

I have no problem with PQ, I just started the process in DAX, so that was the direction I figured I'd ask for. But if you know the solution using PQ, please let me know!

[Anonymous]

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"

[tamerj1]

Hi @Anonymous 
Is this a source table or a matrix visual? Are you looking for a measure or a calculated table?

[Anonymous]

This is a source table, and I'm looking to create a calculated table. Thanks!