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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
nephologist
Frequent Visitor

Sum over two unrelated tables

Hello there,

 

I'm trying to perform a calculation over two unrelated tables, which are connected through a third table.

 

Suppose you have TableA with an "Accounting Key" number, and its corresponding "Amount". These amounts sum up to a grand total of X. On TableB you have a "Expenditure Key" instead, and its matching "Amount". These amounts sum up to Y, which could be different from X. That difference is what I want to quantify.

 

They are both related to a Catalogue1 table, which lists each of the Expenditure Keys and how they're sorted under the Accounting Key. Something like the following, except the tables just exist in the Data Model:


dax problem.jpg

 

I would like to have a calculated column in TableA that shows the difference between the aggregations of the amounts in TableA and TableB, so that I can later visualize, by Account Key, these differences. Do you know how to do it?

 

Any help would be greatly appreciated.

 

EDIT:

I would expect something like this to be returned (given the example above) (made explicit the sum in the last column):

 

Account KeyAmountAmount by Expenditure (sum)
M12399(12+34+56) = 102
N456111(89+67+23) = 179
P789234(78+45) = 123
1 ACCEPTED SOLUTION
Phil_Seamark
Microsoft Employee
Microsoft Employee

Hi @nephologist

 

If you add the following calculated column to TableA it should work

 

Amount by Expenditure (sum) = CALCULATE(SUM(TableB[Amount]),RELATEDTABLE('TableB'))

This relies on relationships being set correctly.  You'll need a single direction 1 to many between TableA and Catalogue1, then a bi-directional relationship between Catalogue1 and TableB

 

Please download a copy of this in a PBIX file here

 

https://1drv.ms/u/s!AtDlC2rep7a-oC2ePUvD1O4KoIc8


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

7 REPLIES 7
Phil_Seamark
Microsoft Employee
Microsoft Employee

Hi @nephologist

 

If you add the following calculated column to TableA it should work

 

Amount by Expenditure (sum) = CALCULATE(SUM(TableB[Amount]),RELATEDTABLE('TableB'))

This relies on relationships being set correctly.  You'll need a single direction 1 to many between TableA and Catalogue1, then a bi-directional relationship between Catalogue1 and TableB

 

Please download a copy of this in a PBIX file here

 

https://1drv.ms/u/s!AtDlC2rep7a-oC2ePUvD1O4KoIc8


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thanks for your input, Phil.

 

By a "bi-directional relationship" between Catalogue1 and TableB, I take you mean that the arrows in the diagram view between these two tables show two pointy ends, right?

 

What if the relationship is not bijective, but is as in the diagram I first used? Unfortunately my data behaves exactly like this: each Expenditure Key has multiple inputs.

 

For example, how would it be possible to do it using the following "Expenditure Key" data, and the relationship between Catalogue1 and TableB being: TableB -> Catalogue1 (i.e. one way only)?

 

Expenditure KeyAmount
A12
A21
B23
C34
D45
E56
F67
G78
H89

HI @nephologist

 

Yes, by bi-directional I mean the arrows on the line between the two tables.  When you use a bi-directional relationship, you are in effect, merging the two tables together into 1, which allows you to traverse between tables you otherwise wouldn't.

 

For more complex data scenarios you might consider creating summary tables via calculated tables.  So create a new table that combines and groups the columns in TableB and Catalougue1 together in one table.

 

Mind you, I just changed my relationship between TableB and Catalogue1 to single direction and it worked as well.

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Just to clarify further, so long as the relationship between Catalogue1 to TableB is 1 to many, with Catalog1 on the 1 side, my suggestion should work.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hey again,

 

 

EDIT: Your solution worked out, so long as the active relationships between tables are precisely those as in the examples above. You see, I was trying to implement this solution while having a second catalogue (dates, months) also linked to both TableA and TableB, but it only works if the main, active relationship is the one with CatalogueA. I didn't think mentioning it would be important; after all, it's a different, unrelated catalogue.

 

I know you weren't losing your sleep over this, but hey! Thank you.

parry2k
Super User
Super User

Add following measure in your table A:

 

Total TableA Amount  = SUM(TableA[Amount])

Total TableB Amount = SUM(TableB[Amount])

Difference = [Total TableA Amount] - [Total TableB Amount]

you can do all this one measure as well/



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Phil_Seamark
Microsoft Employee
Microsoft Employee

Hi @nephologist

 

Can you please post what your expected output might be for your small example?

 

This will help clarify your requirement.

 

Cheers,

 

Phil 🙂


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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