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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
david2
Helper I
Helper I

Summarizing two different tables

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

 

2 ACCEPTED SOLUTIONS
BhaveshPatel
Community Champion
Community Champion

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 queriesAppend as new queriesGroupbyGroupbyFinal outcomeFinal outcome

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

BhaveshPatel
Community Champion
Community Champion

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 queriesAppend as new queriesGroupbyGroupbyFinal outcomeFinal outcome

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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