Union 2 tables then Groupby with calculation on union

[Alex_pg]

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
)

NAME	AMOUNT VALUES1	AMOUNT VALUES2
Name 1	5	0
Name 2	3	0

Summarize table 2:

SUMMARIZECOLUMNS(

   TBL2.NAME

   ,"AMOUNT VALUES1"

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

NAME	AMOUNT VALUES1	AMOUNT VALUES2
Name 1	0	2
Name 2	0	2
Name 3	0	2

The Union Table:

UNION(

SUMMARIZECOLUMNS(

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

   ,"AMOUNT VALUES2"

   ,0
)

,

SUMMARIZECOLUMNS(

   TBL2.NAME

   ,"AMOUNT VALUES1"

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

)

NAME	AMOUNT VALUES 1	AMOUNT VALUES 2
Name 1	5	0
Name 2	3	0
Name 1	0	2
Name 2	0	2
Name 3	0	2

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

NAME	AMOUNT VALUES 1	AMOUNT VALUES 2
Name 1	5	2
Name 2	3	2
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

[barritown · Accepted solution]

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:

[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

[Alex_pg]

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