Power Bi Desktop - How to add values between tables?

Question

I'm trying to create a column that has a total of values between 3 columns from 3 tables. How would I go about doing this?

The 2 tables are tables of values that share an id, and they are both linked to a table of account by Id. The goal is to add up 3 columns, and place it into a table grouped by the Id.

I've attempted summing them, trying to use the USERELATIONSHIP function, and creating a relationship between them. It seems to give very inaccurate results, as if it's summing all of the totals together, and passing them to each Id. That, or it won't let me use the column, as if it never existed.

EDIT: General Idea of what I'm trying to do (Lines should be pointing to Account's Id column, but I messed up the lines)

EDIT 2: I also forgot to illustrate or mention. There are more columns with information in each table that can't be summarized for each account preventing me from just merging the table together.

Welcome to Stack Overflow. Please add an image of your data model. A data sample will be helpful too. — RADO
Thank you, I'm posting a sample of what it kind of looks like with fake data. I don't feel comfortable posting any actual information regarding the dataset, but it should be fairly representative of the problem. I can post a more specific error message when I get a chance as well. — Sabuus

RADO RADO · Accepted Answer · 2019-06-27T06:25:18

Make sure your data model looks like this (change names as you please, but the structure must be the same):

In dimensional modeling, your table "Account" is a Dimension, and both fee tables are Fact tables. The operation of combining data from multiple fact tables that share the same dimension is called "drill-across", and it's a standard functionality of Power BI.

To combine fees from these tables, you just need to use measures, not columns. This article explains the difference:

Calculated Columns and Measures in DAX

First, create 2 measures for the fees:

Fee1 Amount = SUM(Fee_1[Amount])

Fee2 Amount = SUM(Fee_2[Amount])

Then, create a third measure to combine them:

Total Fee Amount = [Fee1 Amount] + [Fee2 Amount]

Create matrix visual, and place Account_ID from the Account table on the rows. Then drop all these measures into the matrix values area, like this:

Result:

Of course, you don't have to have all these measure in the matrix, I just showed them for your convenience, to validate the results. If you remove them, the last measure still works:

Power Bi Desktop - How to add values between tables?

1 Answers