topic Re: Add Total current balance for Customers with multiple accounts in DAX Commands and Tips

Add Total current balance for Customers with multiple accounts

KW123 — Wed, 09 Nov 2022 05:09:18 GMT

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!

Re: Add Total current balance for Customers with multiple accounts

ValtteriN — Wed, 09 Nov 2022 09:34:52 GMT

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/

Re: Add Total current balance for Customers with multiple accounts

KW123 — Wed, 09 Nov 2022 15:53:07 GMT

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.

Re: Add Total current balance for Customers with multiple accounts

djurecic — Wed, 09 Nov 2022 16:51:52 GMT

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:

Re: Add Total current balance for Customers with multiple accounts

KW123 — Wed, 09 Nov 2022 16:54:45 GMT

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

Re: Add Total current balance for Customers with multiple accounts

djurecic — Wed, 09 Nov 2022 16:58:48 GMT

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.

Re: Add Total current balance for Customers with multiple accounts

djurecic — Wed, 09 Nov 2022 17:20:15 GMT

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]))

Re: Add Total current balance for Customers with multiple accounts

KW123 — Wed, 09 Nov 2022 17:28:59 GMT

djurecic

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.

Re: Add Total current balance for Customers with multiple accounts

djurecic — Wed, 09 Nov 2022 17:38:16 GMT

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?

Re: Add Total current balance for Customers with multiple accounts

KW123 — Wed, 09 Nov 2022 17:38:11 GMT

djurecic
djurecic

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.

Re: Add Total current balance for Customers with multiple accounts

KW123 — Wed, 09 Nov 2022 17:40:23 GMT

djurecic
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

Re: Add Total current balance for Customers with multiple accounts

djurecic — Wed, 09 Nov 2022 17:41:34 GMT

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

Re: Add Total current balance for Customers with multiple accounts

KW123 — Wed, 09 Nov 2022 17:44:30 GMT

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.

Re: Add Total current balance for Customers with multiple accounts

djurecic — Wed, 09 Nov 2022 17:46:34 GMT

What Dax are you using for "Customer Balance2" ?

Re: Add Total current balance for Customers with multiple accounts

KW123 — Wed, 09 Nov 2022 18:09:32 GMT

djurecic

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

Re: Add Total current balance for Customers with multiple accounts

djurecic — Wed, 09 Nov 2022 18:29:23 GMT

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

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

Re: Add Total current balance for Customers with multiple accounts

KW123 — Wed, 09 Nov 2022 18:39:29 GMT

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

Re: Add Total current balance for Customers with multiple accounts

djurecic — Wed, 09 Nov 2022 18:49:23 GMT

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.

Re: Add Total current balance for Customers with multiple accounts

KW123 — Wed, 09 Nov 2022 18:53:21 GMT

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

Re: Add Total current balance for Customers with multiple accounts

djurecic — Wed, 09 Nov 2022 18:57:34 GMT

You're welcome!

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