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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Alex_pg
Advocate I
Advocate I

Union 2 tables then Groupby with calculation on union

Hi, 

I am trying to summarize/groupby a Unioned table.

 

I use a combination of UNION(SUMMARIZECOLUMNS(...),SUMMARIZECOLUMS(...)) and would like to summarize further this table so that I only have one unique row per category (Name).

 

Summarize table 1:

SUMMARIZECOLUMNS(

   TBL1.NAME
   ,"AMOUNT VALUES1"
   ,SUM(Value1)

   ,"AMOUNT VALUES2"

   ,0
)

NAMEAMOUNT VALUES1AMOUNT VALUES2
Name 1  50
Name 2   30

 

Summarize table 2:

SUMMARIZECOLUMNS(

   TBL2.NAME

   ,"AMOUNT VALUES1"

   ,0
   ,"AMOUNT VALUES2"
   ,SUM(Value2)
)

NAMEAMOUNT VALUES1AMOUNT VALUES2
Name 1   02
Name 2   02
Name 3   02

 

The Union Table:

UNION(

SUMMARIZECOLUMNS(

   TBL1.NAME
   ,"AMOUNT VALUES1"
   ,SUM(Value1)

   ,"AMOUNT VALUES2"

   ,0
)

,

SUMMARIZECOLUMNS(

   TBL2.NAME

   ,"AMOUNT VALUES1"

   ,0
   ,"AMOUNT VALUES2"
   ,SUM(Value2)
)

)

 

NAMEAMOUNT VALUES 1AMOUNT VALUES 2
Name 1   50
Name 2   30
Name 1   02
Name 2   02
Name 3   02

 

 

The problem:
I want the table above to be summarized further in this manner:

NAMEAMOUNT VALUES 1AMOUNT VALUES 2
Name 1   52
Name 2   32
Name 3    2

 

But When I try to include a GROUPBY or SUMMARIZECOLUMN on top of the union I am not able to sum the "AMOUNT VALUES" as it is not detected.

This is what I tried

SUMMARIZECOLUMNS(
   UNIONTABLE
  ,NAME --> it gets picked up OK

  ,"AMOUNT1"

  ,SUM( I cannot chose the "AMOUNT VALUES1" created earlier)

 

Any thought on how to get it to work?

 

Regards,

 

Alex

 

1 ACCEPTED SOLUTION
barritown
Super User
Super User

Hi @Alex_pg,

Indeed, on my PC the engine's behaviour is the same. 

But you can solve your task in a different way without UNION:

barritown_0-1685608339338.png

[DAX] code in plain text:

Res = 
ADDCOLUMNS ( 
    DISTINCT ( UNION ( VALUES ( TBL1[Name] ), VALUES( TBL2[Name]) ) ),
    "Amt1", 
    VAR CurrentName = [Name]
    RETURN SUMX ( FILTER ( TBL1, [Name] = CurrentName ), [Value1] ), 
    "Amt2", 
    VAR CurrentName = [Name]
    RETURN SUMX ( FILTER ( TBL2, [Name] = CurrentName ), [Value2] ) )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

View solution in original post

2 REPLIES 2
barritown
Super User
Super User

Hi @Alex_pg,

Indeed, on my PC the engine's behaviour is the same. 

But you can solve your task in a different way without UNION:

barritown_0-1685608339338.png

[DAX] code in plain text:

Res = 
ADDCOLUMNS ( 
    DISTINCT ( UNION ( VALUES ( TBL1[Name] ), VALUES( TBL2[Name]) ) ),
    "Amt1", 
    VAR CurrentName = [Name]
    RETURN SUMX ( FILTER ( TBL1, [Name] = CurrentName ), [Value1] ), 
    "Amt2", 
    VAR CurrentName = [Name]
    RETURN SUMX ( FILTER ( TBL2, [Name] = CurrentName ), [Value2] ) )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

I completely forgot to reply.
Thank you very much, it worked perfectly.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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