Sum for all combinations of attributes

[mostho]

Assume there is a table with two columns, one for attributes (eg. A, B, C, D, ... or maybe years: 2019, 2020, 2021, 2022, ... ) and one for a summable value.
Lets say we have:

attribute	value
A	10
B	15
C	8
D	17

the result should be:

resultattr	value
A	10
B	15
C	8
D	17
AB	25
AC	18
AD	27
BC	23
BD	32
CD	25
ABC	33
ABD	42
ACD	35
BCD	40
ABCD	50

The first column idoes not have to be in this form (ABD), it could be the binary representation (0101 -> BD)

[lbendlin · Accepted solution]

This one is a little more efficient 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0UIrViVZyAjFNwUxnINMCzHIBCZorxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [attribute = _t, value = _t]),
    #"Replaced Value" = Table.ReplaceValue(Table.SelectColumns(Source,{"attribute"}),each [attribute],each {[attribute]},Replacer.ReplaceValue,{"attribute"}),
    semicart = (tbl) =>
    let 
        #"Added Custom" = Table.AddColumn(tbl, "Custom", each Source[attribute]),
        #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
        #"Replaced Value1" = Table.ReplaceValue(#"Expanded Custom",each [attribute],each List.Sort(List.Distinct([attribute] & {[Custom]})) ,Replacer.ReplaceValue,{"attribute"}),
        #"Removed Other Columns" = Table.Distinct(Table.SelectColumns(#"Replaced Value1",{"attribute"}))
    in
        #"Removed Other Columns",
    res1 = semicart(#"Replaced Value"),
    res2 = semicart(res1),
    res3 = semicart(res2),
    #"Added Index" = Table.AddIndexColumn(res3, "Index", 0, 1, Int64.Type),
    #"Expanded attribute" = Table.ExpandListColumn(#"Added Index", "attribute"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded attribute", {"attribute"}, Source, {"attribute"}, "Expanded attribute", JoinKind.LeftOuter),
    #"Expanded Expanded attribute" = Table.ExpandTableColumn(#"Merged Queries", "Expanded attribute", {"value"}, {"value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Expanded attribute",{{"value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Index"}, {{"resultattr", each Text.Combine([attribute]), type nullable text}, {"value", each List.Sum([value]), Int64.Type}})
in
    #"Grouped Rows"

and it can be adapted to the number of cartesian products.

[rajendraongole1 · Accepted solution]

Hi @mostho - You can try solutions suggested in power query editor by @lbendlin  @AmiraBedh 

I have tried with DAX as below:

I have created a calculated table as per the input data:

CombinationsTable =

ADDCOLUMNS(

    GENERATE(

        SUMMARIZECOLUMNS(AttributeTable[attribute]),

        SELECTCOLUMNS(

            GENERATESERIES(1, POWER(2, COUNTROWS(ALL(AttributeTable[attribute]))) - 1, 1),

            "Combination", [Value]

        )

    ),

    "Bin", POWER(2, RANKX(ALL(AttributeTable[attribute]), AttributeTable[attribute]) - 1),

    "ResultAttr", CONCATENATEX(

        FILTER(

            ALL(AttributeTable),

            BITAND([Combination], POWER(2, RANKX(ALL(AttributeTable[attribute]), AttributeTable[attribute]) - 1)) > 0

        ),

        AttributeTable[attribute],

        ""

    )

)

Another calculated table to get the each combination of the values. 

CombinationSumsTable =

SUMMARIZE(

    CombinationsTable,

    [ResultAttr],

    "Value", CALCULATE(

        SUM(AttributeTable[value]),

        FILTER(

            AttributeTable,

            CONTAINSSTRING([ResultAttr], AttributeTable[attribute])

        )

    )

)

to sort it,

SortOrder =

VAR Length = LEN([ResultAttr])

RETURN

Length * 1000 +

SUMX(

    ADDCOLUMNS(

        GENERATESERIES(1, Length, 1),

        "CharValue", UNICODE(MID([ResultAttr], [Value], 1))

    ),

    [CharValue]

)

hope it helps

[AmiraBedh]

I would go for Power Query instead of DAX.

[lbendlin]

This is based on the sample data you provided.  I am very sure this can be optimized.  As you probable appreciate there is always a danger that cartesian products will get out of hand, and fast.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0UIrViVZyAjFNwUxnINMCzHIBCZorxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [attribute = _t, value = _t]),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"attribute"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "attribute1", each Source[attribute]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "attribute1"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "attribute2", each Source[attribute]),
    #"Expanded Custom1" = Table.ExpandListColumn(#"Added Custom1", "attribute2"),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom1", "attribute3", each Source[attribute]),
    #"Expanded attribute3" = Table.ExpandListColumn(#"Added Custom2", "attribute3"),
    #"Added Custom3" = Table.AddColumn(#"Expanded attribute3", "Custom", each List.Sort(List.Distinct(Record.ToList(_)))),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom3",{"Custom"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Other Columns1", {"Custom", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Removed Duplicates" = Table.Distinct(#"Extracted Values"),
    #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 0, 1, Int64.Type),
    #"Split Column by Position" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{"Custom", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    #"Merged Queries" = Table.NestedJoin(#"Split Column by Position", {"Custom"}, Source, {"attribute"}, "Split Column by Position", JoinKind.LeftOuter),
    #"Expanded Split Column by Position" = Table.ExpandTableColumn(#"Merged Queries", "Split Column by Position", {"value"}, {"value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Split Column by Position",{{"value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Index"}, {{"value", each List.Sum([value]), type nullable text}, {"resultattr", each Text.Combine([Custom]), type nullable text}})
in
    #"Grouped Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[lbendlin · Accepted solution]

This one is a little more efficient 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0UIrViVZyAjFNwUxnINMCzHIBCZorxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [attribute = _t, value = _t]),
    #"Replaced Value" = Table.ReplaceValue(Table.SelectColumns(Source,{"attribute"}),each [attribute],each {[attribute]},Replacer.ReplaceValue,{"attribute"}),
    semicart = (tbl) =>
    let 
        #"Added Custom" = Table.AddColumn(tbl, "Custom", each Source[attribute]),
        #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
        #"Replaced Value1" = Table.ReplaceValue(#"Expanded Custom",each [attribute],each List.Sort(List.Distinct([attribute] & {[Custom]})) ,Replacer.ReplaceValue,{"attribute"}),
        #"Removed Other Columns" = Table.Distinct(Table.SelectColumns(#"Replaced Value1",{"attribute"}))
    in
        #"Removed Other Columns",
    res1 = semicart(#"Replaced Value"),
    res2 = semicart(res1),
    res3 = semicart(res2),
    #"Added Index" = Table.AddIndexColumn(res3, "Index", 0, 1, Int64.Type),
    #"Expanded attribute" = Table.ExpandListColumn(#"Added Index", "attribute"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded attribute", {"attribute"}, Source, {"attribute"}, "Expanded attribute", JoinKind.LeftOuter),
    #"Expanded Expanded attribute" = Table.ExpandTableColumn(#"Merged Queries", "Expanded attribute", {"value"}, {"value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Expanded attribute",{{"value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Index"}, {{"resultattr", each Text.Combine([attribute]), type nullable text}, {"value", each List.Sum([value]), Int64.Type}})
in
    #"Grouped Rows"

and it can be adapted to the number of cartesian products.