Forum Discussion
Sum from Multiple Tables
I couldn't find what I needed in older posts, so here we go.
I have over half a dozen revenue reports that I need to combine by customer account, location, and by fiscal date ranges. I have some reference tables that help normalize the data (e.g. not all accounts have identical names, so I have a fuzzy matching table that I've been using. I have a date conversion table with the start & end dates for each quarter and another to get all the locations grouped the way I need). I can get a table summarized by location, date range, account, or a combination of two out of the three I need.
I have each data table & appropriate columns related, all bi-directional. By this I mean that I have Report 1, 2, etc. Account Name column related to my Fuzzy Account table, all the date columns (for ranges, I link based on start date, but more of a lack of knowledge on how to get a range to apply) referring to my Fiscal Calendar table, and all the location columns linked to the location grouping table. I've tried playing around with which group is active (e.g. all location column links) and USERELATIONSHIP in various calculated columns and measures.
This is what I want:
But this is how I usually receive the data:
Any help would be greatly appreciated. Thanks!
4 Replies
datadude2020 , You have to do some data modeling/transformation to have common dimensions of date , location account and country. Also Country code different that you have to match
amitchandak Yes, I have. As I said, I have various reference tables & calculated columns to make everything consistant on the outside. The only piece of that I haven't figured out is how to deal with date ranges vs. single dates. So on the visual pages, I have columns available for use that are pulling from the connected tables. So FR and France appear as Europe, and US & USA appear as North America, to use my earlier examples.
datadude2020 , Thrid one is simple, You can join on start or end date. And you will see data at month level. Second data seems to be at year level. Have year a number or same as FY in calendar. One option is, that do not join and always have a measure that take date and find its year
Like
Measure= Var _max = year(maxx(allselected(Date),Date[Date]))
return
calculate(sum(Report2[Value]), Report2[Value] =_max)
Another way is to move the data year start date.
Date = Date([Year],1,1)
But data will not be Shown in any other month.
