Sum transaction by groupings

[Anonymous]

I have a dataset with the following:

accountgroup	transaction
10	88493,01
11	2992,34
11	2141,1
10	23754,97
12	342511
10	23561,34
12	85,75
...	...
70	-66754,98
71	-47293,21

How can I sum the transactions of accountgroup 10, 11 and 12 all into one value? So combining the sum of accountgroup 10, 11 and 12 but still keep the remaining other accountgroups as is.

I'm new to Power BI and I've been testing around with DAX.

[v-yiruan-msft]

Hi @Anonymous ,

According to your description, you want to merge [accountgroup]=10 | 11 | 12, leave the others unchanged, and generate a new table.Right?

Here are the steps you can follow：

(1)This is my test data:

(2)We can click “New Table” and enter:

Table =
VAR _group =
    SUMX (
        FILTER ( 'test', 'test'[accountgroup] IN { 10, 11, 12 } ),
        'test'[transaction]
    )
VAR _tb1 = { ( "10|11|12", _group ) }
VAR _group2 =
    FILTER ( 'test', NOT 'test'[accountgroup] IN { 10, 11, 12 } )
RETURN
    UNION (
        SELECTCOLUMNS ( _tb1, "accountgroup", [Value1], "transaction", [Value2] ),
        _group2
    )

(3)Then we can meet your need, the result is as follows:

If this method does not meet your needs, can you provide detailed output sample data, we can help you better.

Best Regards

[Anonymous]

Hello, I enter exactly your DAX, except changing 'Test' to be name of my dataset. But, I recieve following error: 
All table arguments in UNION must contain the same number of columns.

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

According to your description, the UNION() function reports an error. The main reason is that the premise of using the UNION function is that the number of columns between the two tables must be the same. The main reason is that your original table does not have two columns of data.Right?

(1)This is my test data:

(2)We can modify our previous DAX function that created the table:

Table =
VAR _group =
    SUMX (
        FILTER ( 'test', 'test'[accountgroup] IN { 10, 11, 12 } ),
        'test'[transaction]
    )
VAR _tb1 = { ( "10|11|12", _group ) }
VAR _group2 =
    SELECTCOLUMNS (
        FILTER ( 'test', NOT 'test'[accountgroup] IN { 10, 11, 12 } ),
        "accountgroup", [accountgroup],
        "transaction", [transaction]
    )
RETURN
    UNION (
        SELECTCOLUMNS ( _tb1, "accountgroup", [Value1], "transaction", [Value2] ),
        _group2
    )

(3)Then we can solve this error message and meet your requirements.

Best Regards

[daXtreme]

Change the data model a bit... Instead of your table, create this one (add a column to the one you have):

accountgrouping | accountgroup | transaction

where the accountgrouping will be this DAX (as a calculated column):

if( T[accountgroup) in {10, 11, 12}, "10|11|12", T[accountgroup] )

Then just slice by this column. The measure will be:

sum( T[transaction] ).