cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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:

RegionMonthFigure
EuropeJan 18100
EuropeFeb 18200
EuropeMar 18100
AsiaJan 1850
AsiaFeb 1870
AsiaMar 18100

 

Table 2:

MonthFigure 2
Jan 181
Jan 182
Feb 182
Mar 184
Mar 181
Mar 183

 

Table I want:

 

MonthFigureFigure 2
Jan 181503
Feb 182702
Mar 182008

 

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VII
Super User VII

Re: Making a summary table from two different tables?

@Saj94 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-ca...

 

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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

3 REPLIES 3
Highlighted
Super User VII
Super User VII

Re: Making a summary table from two different tables?

@Saj94 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-ca...

 

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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
Super User IX
Super User IX

Re: Making a summary table from two different tables?

@Saj94 , 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]))


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I
Helper I

Re: Making a summary table from two different tables?

@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?

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors