Forum Discussion
Making a summary table from two different tables?
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?
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.⚡
3 Replies
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.⚡
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]))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?
