Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

Sum transaction by groupings

I have a dataset with the following:

 

accountgrouptransaction
1088493,01
112992,34
112141,1
1023754,97
12342511
1023561,34
1285,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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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:

yingyinr_0-1662370528280.png

 

(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.

yingyinr_1-1662370528283.png

Best Regards

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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:

yingyinr_0-1662097325098.png

 

(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:

yingyinr_1-1662097325101.png

 

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

 

Best Regards

Anonymous
Not applicable

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.

Anonymous
Not applicable

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:

yingyinr_0-1662370528280.png

 

(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.

yingyinr_1-1662370528283.png

Best Regards

 

daXtreme
Solution Sage
Solution Sage

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] ).

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.