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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
cdfisher89
Helper I
Helper I

Sum from related table

 

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.

 

2017_05_16_11_30_56_Tables.xlsx_Excel.png

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Try:

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

 

The calculate is weird and required.

View solution in original post

11 REPLIES 11
Dan80
Helper II
Helper II

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!

Anonymous
Not applicable

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

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.

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

Anonymous
Not applicable

Try:

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

 

The calculate is weird and required.

What about a SUMIFS formula? if you want to also exclude a category from the sumif formula?

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.

Anonymous
Not applicable

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

Structure:

Structure.png

 

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

2.png

 

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

 

formula.png

I fixed it.  You were right on the formula.  The "key" field was slightly different, which caused all the issues. 

 

Thanks!

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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