Summarizing two different tables

[david2]

Hi there,

I have two tables in my data model that contain marketing costs for our organisation. I would like to combine them into one table and summarize the cost per day. As both tables are different, i can't merge the queries (and i also would like to retain the individual tables).

Example:

Table A has marketing costs for 1/1, 2/1 and 4/1 for let's say 10 euro each.

Table B has marketing costs for 2/1, 3/1 and 5/1 for 10 euro each

Table C (the designated new table) would have:
1/1: 10

2/1: 20

3/1: 10

4/1: 10

5/1: 10

Can anyone explain me how to do that? 

Thanks!

David

[BhaveshPatel · Accepted solution]

You can use Append to achieve this. Select Append as new Queries, Append Table B on the Table A and then You can do GROUPBY,

Group by Date, Operation Sum and Column is Euro. This will give you the desired result. See the attached screenshot.

Append as new queriesGroupbyFinal outcome

[v-yulgu-msft · Accepted solution]

Hi @david2，

That combining two tables can be easily achieved using DAX, please refer to below formulas to create new tables:

Table1 = UNION(TableA,TableB)
TableC = SUMMARIZE(Table1,Table1[Date],"Costs",SUM(Table1[Costs]))

If you have any other question, please feel free to ask.

Best regards,
Yuliana Gu

[Anonymous]

Do the source tables have to be the same granularity (cardinality) for Union and Append to work?

[BhaveshPatel · Accepted solution]

You can use Append to achieve this. Select Append as new Queries, Append Table B on the Table A and then You can do GROUPBY,

Group by Date, Operation Sum and Column is Euro. This will give you the desired result. See the attached screenshot.

Append as new queriesGroupbyFinal outcome

[Anonymous]

I have a similar query, but I would like to see values from Table A and Table B in seperate columns