Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Sum from related table

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

cdfisher89

Helper I

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-16-2017
09:35 AM

I am trying to sum values that are on other tables onto a master table, but I can't seem to get it to work. It is essentially a Sumif function, but I can't seem to get Calculate and or SUMX to work. It always wants to return the fee for the entire related table (Revenue or Time).

Please see the below image. I am trying to sum Fees and Hours onto the Assignments table for the individual assignments. The Revenue and Time tables are linked to Assignments.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

scottsen

Memorable Member

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-16-2017
12:23 PM

Try:

AssignmentTable[Total Fee] = CALCULATE(SUM(Revenue[Fee]))

The calculate is weird and required.

10 REPLIES 10

Highlighted
##

scottsen

Memorable Member

Re: Sum from related table

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-16-2017
11:29 AM

Your "What is Happening" is confusing on the Assignment table -- I assume there are actually 3 rows there?

Highlighted
##

cdfisher89

Helper I

Re: Sum from related table

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-16-2017
12:11 PM

Sorry. It isn't summing up at the relevant rows. It is just giving me a total for all assignments. I would like the total fees and hours for each assignment based on the other tables.

Highlighted
##

cdfisher89

Helper I

Re: Sum from related table

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-16-2017
12:13 PM

You can disregard the bottom half. I am just trying to create the Fee and Hours columns in the Assignment Table

Highlighted

scottsen

Memorable Member

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-16-2017
12:23 PM

Try:

AssignmentTable[Total Fee] = CALCULATE(SUM(Revenue[Fee]))

The calculate is weird and required.

Highlighted
##

Dan80

Helper II

Re: Sum from related table

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-16-2017
12:36 PM

Best to use power query/query editor then bring the data through in one table.

Start with 'group by' function which is basically a sumif and do this for both the revenue table and the time table. This will produce one summed up value for each assignment. Then merge the time table into the revenue table, linking them by the assignment, select the hours colum to bring through to the revenue table. This will produce what you need 3 rows and 2 columns of data. Boom!

Highlighted
##

cdfisher89

Helper I

Re: Sum from related table

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-16-2017
12:45 PM

Didn't work. It seems to just calculate the total rather than for each assignment. I tried both as a measure and as a column.

Highlighted
##

cdfisher89

Helper I

Re: Sum from related table

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-16-2017
01:09 PM

I feel that this one is asking the same question (I have a one to many relationship), but i havent been able to get it to work http://community.powerbi.com/t5/Desktop/Create-a-Calculated-Column-from-Different-Tables/m-p/60773#M...

Highlighted
##

scottsen

Memorable Member

Re: Sum from related table

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-16-2017
04:42 PM

Can you show a picture of your model relationships -- this should have worked fine...

Highlighted
##

cdfisher89

Helper I

Re: Sum from related table

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-18-2017
09:33 AM

Structure:

Relationships: The tables are connected via an Assignment Key field.

Formula: HHG = CALCULATE(SUM('Assignment Schedule Split'[Revenue (USD)])).

- The fields on the left of HHG are from the Assignment table.

- The formula is on the assignments table