Add Total current balance for Customers with multiple accounts

[KW123]

I think I need an IF statement.

I need to show Customer ID's with a total current account balance of $0.00.  Each Customer ID can have multiple account types with different balances.  I need to pull only the Customer ID with a current total balance of $0.00 based on the current month. 

Customer ID	Account Number	Current Balance
1	111	$0.00
1	112	$0.00
2	113	$100
2	114	$0.00
2	115	$200
3	116	$0.00
4	117	$500
5	118	$0.00
5	119	$0.00

So only customer ID 1, 3, 5 should show up in our filter as their total account balance is $0.00 for this month. 

Thank you!

[djurecicK2 · Accepted solution]

Can you please try this? Modify to fit your field names.

Customer Balance2 = CALCULATE(CALCULATE(SUM(Query1[CurrentBalance])),ALLEXCEPT(Query1,Query1[Customer ID]))

[KW123]

@djurecicK2 
This seems to be on the right track! Is there a way to add a date filter so that it's not adding the Customers lifetime balance but their current month balance? 

Right now the balance is the same for all accounts which we want, however, it is just adding up the lifetime balances instead of current balance. 

[djurecicK2]

Yes- you need to add a date table to your report if you have not already done it.

You can download a script here if you don't have one:

https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/

Then create a relationship between Query1 and the date table.

Then add a slicer to your report page and set that to the current month.

Please mark as solution if you believe this has solved the issue.

[KW123]

Customer Balance2 = CALCULATE(CALCULATE(SUM(Query1[CurrentBalance])),ALLEXCEPT(Query1,Query1[AccountNumber]), 'Dates'[Date] = MAX ( 'Dates'[Date] ) )

I got it!! Thanks so much for your help I really appreciate it 

[djurecicK2]

You're welcome!

Please mark as solution if you believe this has solved the issue.

[djurecicK2]

What Dax are you using for "Customer Balance2" ?

[KW123]

@djurecicK2 

Customer Balance2 = CALCULATE(SUM(Query1[CurrentBalance]),REMOVEFILTERS(Query1)[accountnumber]))

[djurecicK2]

There was an error with the original measure I posted: here is an update which will allow the first table to work:

Customer Balance = CALCULATE(CALCULATE(SUM(Balance[Current Balance])),REMOVEFILTERS(Balance[Account Number]),REMOVEFILTERS(Balance[Current Balance]))

[KW123]

@djurecicK2 

I am not sure what I am doing incorrectly.  Here is a screen grab of what is happening.  

I used Customer Balance = CALCULATE(CALCULATE(SUM(Balance[Current Balance])),REMOVEFILTERS(Balance[Account Number]),REMOVEFILTERS(Balance[Current Balance]))

As you can see, Customer ID  1 has both closed accounts with a combined balance of 0.  The account numbers for each row are different, so a customer ID will have different account numbers. 

Customer ID 2, 3, 4, all have a balance of 0, but they also have an open account with a balance >0 so in this case, I only want Customer ID to return.  

[djurecicK2]

You've added columns to the matrix, so you need to filter those out to get the total per customer.

Since there are a lot of columns, instead of removing filters individually you can try something like this:

Customer Balance = CALCULATE(CALCULATE(SUM(Balance[Current Balance])),ALLEXCEPT(Balance,Balance[Customer ID]))

Also, is that table fitlered on Current Balance is 0?

[KW123]

@djurecicK2 
I can't filter it to current balance of $0 because customer ID has an open account with a balance >0.  If I filter it to 0 then it will return customer ID 1 but that is false as customer ID 1 still has an open account with >0 balance.  I am only looking to return customers with a total of 0 across all of their accounts 

[djurecicK2]

Sorry meant to say "Customer Balance" not "Current Balance"

[KW123]

Ideally yes, but it's not adding the accounts up correctly.  This customer is the same customer ID but it doesn't appear to be adding all accounts correctly. 

[djurecicK2]

You could use a measure like this (assuming the table name is "Balance")

Customer Balance = CALCULATE(SUM(Balance[Current Balance]),REMOVEFILTERS(Balance[Account Number]))

which gives the following result

Then filter the visual where Cutomer Balance is 0 which gives the following result:

[KW123]

@djurecicK2  Thank you!

Something like this would work but in your example I wouldn't want customer ID 2 to come back since they still have a balance of $200 if you combine all of their accounts. 

Lets say that if the customer account balance is $0, then that account would be considered closed.  If the balance is >$0 the account is open. 

I am looking to extract customer ID with ALL closed accounts.  Meaning, even if they have 1 closed account but at least another open account, I don't want them to come up on the report. 

[djurecicK2]

Ok. To do that, just remove the column "Current Balance" from the table which will give the following result. You could also remove Account Number to get only 1 customer record shown like below:

Please mark as solution if you believe this has solved the issue.

[KW123]

@djurecicK2 
@djurecicK2 

I tried it this way too but this is the same customer ID, and it's not adding the two balances together.  One account should be $0 and the other is $100 for example.  So even if I filtered it for $0, it is false as the customer still has an open account with a balance. 

[ValtteriN]

Hi, 

Here is one way to do this:

Create filter measure:

Measure 20 = IF(CALCULATE(SUM('Table (11)'[Current Balance]),ALL('Table (11)'[Account Number]))=0,1,0)

ALL will remove filter context of accounts

Place the measure as a filter:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[KW123]

Hi @ValtteriN 
Thank you for taking the time to respond! 
I have used the measure you created but I don't think it's quite what I am looking for.  Basically, if a customer Id has an account with a balance >0 in addition to an account = 0, then I don't want that to show in my dashboard.  If a customer has multiple accounts with a balance, I also do not want to show it. 

I only want to show customers with a total balanace of 0, across all of their different accounts. 

In my data using the measure you provided, I have a customer id with two different account numbers.  One has a balance of 0, one account has a balance of 100.  The account with 0 produces a measure 20 column of 1 and the account with 100 produces a measure of 0.  So even if I filter out for just the 1, the customers total balance is still not 0. 

I hope that makes sense.