cancel
Showing results for 
Search instead for 
Did you mean: 
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
Microsoft
Microsoft

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

3 REPLIES 3
v-yulgu-msft
Microsoft
Microsoft

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

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.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.