Making a summary table from two different tables?

[Anonymous]

Hellooo, 

I'm having a few issues trying to create a new table that summarises data from two other tables.

This is the structure of my data

Table 1:

Region	Month	Figure
Europe	Jan 18	100
Europe	Feb 18	200
Europe	Mar 18	100
Asia	Jan 18	50
Asia	Feb 18	70
Asia	Mar 18	100

Table 2:

Month	Figure 2
Jan 18	1
Jan 18	2
Feb 18	2
Mar 18	4
Mar 18	1
Mar 18	3

Table I want:

Month	Figure	Figure 2
Jan 18	150	3
Feb 18	270	2
Mar 18	200	8

Would anyone be able to help me with this? I've tried using the SUMMARIZE function but can't seem to get it to work?

[amitchandak]

@Anonymous , You can have a common month dimension and have these together in a common visual

Or a new Table like

summarize(union(selectcolumns(Table1,"Month",Table1[Month],"Figure",Table1[Figure],"Figure2",0),
				selectcolumns(Table2,"Month",Table2[Month],"Figure",0,"Figure2",Table2[Figure])),
				[Month],"Figure", sum([Figure1]),"Figure2", sum([Figure2]))

[Anonymous]

@amitchandak the formula didn't work as it gives the total sum value for all the months, not the sum for each of the months?

[parry2k · Accepted solution]

@Anonymous not sure if you need to create a summarized table, As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/

Link this date table with both these tabes, and in visual, use month/year from date table and figure 1 and figure 2 from respective tables and you will get the result.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡