Forum Discussion

JuanVR10's avatar
JuanVR10
Frequent Visitor
7 years ago
Solved

Union two tables with different columns and sum the same register

Hi guys,

 

I have the following problem. I have two tables (Table1, Table 2) with this structure:

 

 

Table 1 have these fields: (Material, Desripción, Sales and Stock)

Table 2 have these fields: (Material, Descripción, Sales, Store and TT)

 

As you can see both aren´t identical table. So i need to join the two in a new table, but i have to include both materials in both tables and sum the Sales when the material is the same. For those columns when doesn´t exists in the other table, simply put null.

 

I´m trying to do it with Merge, but put the Material in different columns.

 

Could u help me please?

 

Thanks!!!

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    HI, JuanVR10 

    You could try these two ways as below:

    1. use DAX to create a calculate table by this formula:

    Dax table = VAR _UNIONTABLE=UNION(SELECTCOLUMNS(Table1,"Material",[Material],"Descripcion",[Descripcion],"Sales",[Sales],"Store",BLANK(),"Stock",[Stock],"TT",BLANK()),
SELECTCOLUMNS(Table2,"Material",[Material],"Descripcion",[Descripcion],"Sales",[Sales],"Store",[Store],"Stock",BLANK(),"TT",[TT])) RETURN
GROUPBY(_UNIONTABLE,[Material],[Descripcion],"Sales",SUMX(CURRENTGROUP(),[Sales]),"Store",MAXX(CURRENTGROUP(),[Store]),"Stock",SUMX(CURRENTGROUP(),[Stock]),"TT",SUMX(CURRENTGROUP(),[TT]))

    Result:

     

    2. In Edit Queries, Use Append function and group by function to create a new table

    Result:

     

    here is pbix file, please try it.

     

    Best Regards,

    Lin

3 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    HI, JuanVR10 

    You could try these two ways as below:

    1. use DAX to create a calculate table by this formula:

    Dax table = VAR _UNIONTABLE=UNION(SELECTCOLUMNS(Table1,"Material",[Material],"Descripcion",[Descripcion],"Sales",[Sales],"Store",BLANK(),"Stock",[Stock],"TT",BLANK()),
SELECTCOLUMNS(Table2,"Material",[Material],"Descripcion",[Descripcion],"Sales",[Sales],"Store",[Store],"Stock",BLANK(),"TT",[TT])) RETURN
GROUPBY(_UNIONTABLE,[Material],[Descripcion],"Sales",SUMX(CURRENTGROUP(),[Sales]),"Store",MAXX(CURRENTGROUP(),[Store]),"Stock",SUMX(CURRENTGROUP(),[Stock]),"TT",SUMX(CURRENTGROUP(),[TT]))

    Result:

     

    2. In Edit Queries, Use Append function and group by function to create a new table

    Result:

     

    here is pbix file, please try it.

     

    Best Regards,

    Lin

    • JuanVR11's avatar
      JuanVR11
      Icon for Helper I rankHelper I
      7 years ago

      Thanks a lot my friend

      It works perfectly

       

      Best Regards

      Juan