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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
datadude2020
New Member

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: 

want1.jpg

But this is how I usually receive the data: 

 

raw 1.JPG

 

raw 2.JPG

 

raw 3.JPG

Any help would be greatly appreciated. Thanks!

 

4 REPLIES 4
amitchandak
Super User
Super User

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

@amitchandak  I don't think you're understanding my goal. My main problem here is getting a composite sum for account, date, and location.

 

As I said before, I can get two of the three to work. I can get a composite sum based on account and date or account and location, or by location and date. I need all three together. I already have tables to convert any non-standard data. The example data I posted is raw data - before my standardizations. My problem is combining them after standardization. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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