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

How to sum up and merge data from different tables in certain rows in a new querry?

Hello,

 

I do have a problem how to realize a query regarding different data in a row and multiple columns in tow different and separate tables.

 

The first table contains a column containing a letter for a specific country: in this case A (Austria), CH (Swiss), D (Germany) and a customer code row. The country code appears multiple times in different rows in this table for multiple and different customers.

 

Example Customer A has bought something in January and in March, customer B hast bought different products in August September and December, Customer C hast bought products in January and February.

 

In this Case

 

customer A from Germany (D) appears in the table in JAN and MAR, 

customer B from Swiss (CH) appears in the table in AUG, SEP and DEC,

customer C from Austria (a) appears in the table in JAN and FEB.

 

 

The second table contains a year column, a month column, and the turnover column and the customer code column.

 

What I want to do is to point out for each month in the year 2015 for the customers of these 3 countries in a separate querry:

 

1. row: the respective turnover 2015 for those 3 countries, separated into rows for each month of the year.

2. row: the cumulated turnover 2015 for those 3 countries for every following month, also separated into rows for each month of the year.

 

Can anyone tell me how to implement the query for this?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper III
Helper III

Re: How to sum up and merge data from different tables in certain rows in a new querry?

Hello @v-huizhn-msft,

 

I have solved this Problem by myself.

View solution in original post

18 REPLIES 18
Highlighted
Super User I
Super User I

Re: How to sum up and merge data from different tables in certain rows in a new querry?

@Paddhof1984 i must be honest,  i find it easier if people put screenshots of their data as that really helps to visualise it much easier





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

Proud to be a Super User!




Highlighted
Helper III
Helper III

Re: How to sum up and merge data from different tables in certain rows in a new querry?

TO cumulated.png

Highlighted
Super User I
Super User I

Re: How to sum up and merge data from different tables in certain rows in a new querry?

@Paddhof1984so what is the joining field between the two tables?  is there one?





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

Proud to be a Super User!




Highlighted
Helper III
Helper III

Re: How to sum up and merge data from different tables in certain rows in a new querry?

There are two tables between those two. So the tables 'HKD' and RE2015 are only conected indirectly.

Highlighted
Super User I
Super User I

Re: How to sum up and merge data from different tables in certain rows in a new querry?

@Paddhof1984  well you going to have to bring an intermediary table to link them otherwise how will you aggregate to country if there is no link?





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

Proud to be a Super User!




Highlighted
Helper III
Helper III

Re: How to sum up and merge data from different tables in certain rows in a new querry?

table-relations.JPG

Highlighted
Super User I
Super User I

Re: How to sum up and merge data from different tables in certain rows in a new querry?

@Paddhof1984 do you have a date table?

 

 

this might be a stupid question, i see you have bidirectional relationships, have you created any measure yet and then placed it on a matrix with Country? and what do you get?

 

turnover = sum(TO2015)

cummulative turnover= calculate(turnover), filter(date, date[date] <= MAX ( 'Date'[Date]))

 

the only issues i foresee here is that you do need a date table to get the months

 

 





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

Proud to be a Super User!




Highlighted
Helper III
Helper III

Re: How to sum up and merge data from different tables in certain rows in a new querry?

Ok, I have added the date table.

 

I created a measure where I have choosen Country from HKD and Year, Month and TO2015.

 

I get this:

 

1st attempt.png

Highlighted
Super User I
Super User I

Re: How to sum up and merge data from different tables in certain rows in a new querry?

@Paddhof1984 so is that what you are expecting or not?

so just add the cummulative measure now (will need to be based on a sum(TO2015))  and see if that works for you?

 

 





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

Proud to be a Super User!




Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors