Sum of Balance by Account

[aflintdepm]

I have a list of customers who have multiple claims connected to their account.  I need to calculate the total outstanding balance across all claims, by account number.

In Excel, I would just do a pivot table by account number with a sum of the balance.  For some reason, when I do this in PBI, it is giving the the total balance for the entire roster, not a per-patient sum.  What am I missing?

Data samples:

Patient List (unique, no duplicates, for relationships)

PatientName	AccountNumber
John Smith	12345
Bob Jones	23456
Nancy Lewis	54321
Barbara Taylor	98765

AccountBalances

This is the Many side of the relationship

AccountNumber	ClaimNo	Balance
12345	4556	$45.00
23456	4557	$65.00
12345	4558	$30.00
23456	4559	$25.00
54321	4560	$50.00
98765	4561	$75.00
54321	4562	$40.00
98765	4563	$25.00

My desired output would look like this:

PatientName	Balance
John Smith	$75.00
Bob Jones	$90.00
Nancy Lewis	$90.00
Barbara Taylor	$100.00

I'm sure I'm missing something obvious, so all help is appreciated.

[v-jianpeng-msft · Accepted solution]

Hi, @aflintdepm 

I'm happy to answer your questions. You can use the SUMMARIZE function to get the results you want. Based on the data you provided, I created the following two tables:

I created a new table with the results you are expecting by subtotaling the following DAX expression:

Table = SUMMARIZE('Patient List','Patient List'[PatientName],"Balance",CALCULATE(SUM(AccountBalances[Balance]),FILTER('AccountBalances','AccountBalances'[AccountNumber]='AccountBalances'[AccountNumber])))

The results are as follows:

In the above DAX expression, you can understand it this way: the first parameter is the table that needs to be summarized, the second parameter is to extract the non-duplicate columns in this table, the third parameter is the column name of the summary column, and the fourth parameter The first parameter is the expression of this summary column. In this expression, I sum the Balance in the AccountBalances table and filter out rows with the same AccountNumber through the Filter function. For detailed DAX usage, you can click on the link below to learn and practice.

https://dax.guide/summarize/

https://dax.guide/filter/

https://dax.guide/calculate/

I have provided the PBIX file used this time below. If it can inspire you, that would be great.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Datastronaut]

Hi,

Did you check if the relationship has been created in the model view ?

[aflintdepm]

Yes, and I verified it is 1:Many