7 Replies Latest reply on Oct 30, 2018 3:24 PM by Deepak Rai

# Unique Accounts and Balances

Hello-

I am trying to count the number of unique accounts and balances where user, date, balance, and client number are the same (regardless of the other fields that might be different).

For instance, with this calculation, I would want to be able to get the following numbers based on the information in the photo below.

Balance of unique accounts: 120

Count of unique accounts: 2

Packaged workbook attached.

Thanks!

• ###### 1. Re: Unique Accounts and Balances

{SUM({FIXED User:AVG(Balance)})} Will return 120

{COUNTD(User)} will return 2

• ###### 2. Re: Unique Accounts and Balances

Thank you- this screenshot is only a portion of the data that is in the workbook. Is there a different calculation for the data pictured below where Andrea only worked 2 accounts (based on client number) but there are 3 line items for Andrea? A record is considered duplicate when date, user, client number, and balance are all the same.

Calculation yielding:

Balance of unique accounts: 59

Count of unique accounts: 3

• ###### 3. Re: Unique Accounts and Balances

{SUM({FIXED User, Client Number:AVG(Balance)})} Will return 59

{FIXED User, Client Number:COUNTD(User)} will return 3

• ###### 4. Re: Unique Accounts and Balances

There are also cases where multiple users have the same client number, which would not be considered unique because user is different.

Essentially, I would like anything where date, user, client number, and balance are all the same to be counted 1 time.

Balance of unique accounts (by user/date/balance/client number): 73

Count of unique accounts (by user/date/balance/client number): 5

• ###### 5. Re: Unique Accounts and Balances

{SUM({FIXED User, Activity Name, Client Number:AVG(Balance)})} Will return 73

{FIXED User, Activity Name, Client Number : COUNTD(User)} will return 5

• ###### 6. Re: Unique Accounts and Balances

Thank you!!

• ###### 7. Re: Unique Accounts and Balances