Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Paddhof1984
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

Hello @v-huizhn-msft,

 

I have solved this Problem by myself.

View solution in original post

18 REPLIES 18
Paddhof1984
Helper III
Helper III

TO cumulated.png

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




table-relations.JPG

@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

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

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

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@vanessafvg

 

No, not exactly.

 

What is missing is the cumulative row for the respective month. Also I want to have all 3 figures from the 3 different countries in 1 row for each single month instead showing three times the row for the same month. Are my intentions clear to you or not?

@vanessafvg

 

i keep getting shown this syntax error warning:

 

error.png

 

Can you spot the Syntax Error or have I just made a mistake while referring to a measure or table?

 

Hi @Paddhof1984,

As the @vanessafvg said, the | is wrong, and the date highlighted in bule filter is wrong, you need use a table in filter. The syntax of filter is: FILTER(<table>,<filter>)

1.PNG

Please mix it using the formula @vanessafvg given. If you have resolved your issue, please mark the right reply as answer.

Thanks,
Angelia

@v-huizhn-msft

 

Hello,

 

the suggested solution is correct. But I still got the issue, that the monthly turnover is not added to each following new month. It only shows figures which seem to be added up already in total per month.

 

What I need is, that the turnover is summed up month per month until december for each specific country.

 

So it should look somehow like this:

 

JAN ($ 1.200.000,00)   FEB ($ 1.600.000,00)  MAR ($ 1.300.000,00)

 

MONTH:

 

1                                  2                                3

 

$ 1.200.000,00             $ 2.800.000,00           $ 4.100.000,00      and so on.

 

What I need is: a column where the turnover for specific countries is shown only per month and a second column where the turnover for those countries is shown always summed up with the following month from JAN - DEC.

 

In this case it seems like the suggested query is not containing this solution.

Any other ideas?

Hi @Paddhof1984,

Please try the formula below, and check if it workds fine.

cummulative turnover=
CALCULATE (
    [turnover 15vs16],
    FILTER ( datekey, datekey[Month] <= MAX ( datekey[Month] ) )
)

 

For more details, please refer to this blog.

Thanks,
Angelia

Hi @Paddhof1984,

Have you resolved your issue? Please mark the right reply as answer.

Best Regards,
Angelia

Hello @v-huizhn-msft,

 

I have solved this Problem by myself.

Hi @Paddhof1984,

Congratulations, could you please share your workaround or mark the help reply as answer, so that more people can find the solution easily.

Thanks,
Angelia

@Paddhof1984

 

is your | a ,?

 

i would write it like this;

cummulative turnover=
CALCULATE (
    [turnover 15vs16],
    FILTER ( datekey, datekey[date] <= MAX ( datekey[date] ) )
)

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




vanessafvg
Super User
Super User

@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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.