# I am trying to find Accounts which have multiple Charges assigned to them, I tried to creat a row level Calc to fix it for Account Num,and find the count of charges but i am not getting the right result.

• ###### 2. Re: I am trying to find Accounts which have multiple Charges assigned to them, I tried to creat a row level Calc to fix it for Account Num,and find the count of charges but i am not getting the right result.

i wont be able to attach workbook, due to sensitive nature of the data, but i want to find the accounts which have multiple instances of charges on them, for e.g. the account marked below. When i use Fixed Acc No : Count (Earned Amount), i get count distributed over the span of the dates,hence it returns just 1 count of charge for each month. But i want a collective count through all the time. ( I cant remove date dimension from columns because when i do that  , i get just a single value for earned amount.

• ###### 3. Re: I am trying to find Accounts which have multiple Charges assigned to them, I tried to creat a row level Calc to fix it for Account Num,and find the count of charges but i am not getting the right result.

Try this:

{INCLUDE: SUM(IF [EARNED AMOUNT]>0 THEN 1 END)}

OR

{FIXED [Acc No],YEAR([DATE FIELD]),MONTH([DATE FIELD]): SUM(IF [EARNED AMOUNT]>0 THEN 1 END)}

• ###### 4. Re: I am trying to find Accounts which have multiple Charges assigned to them, I tried to creat a row level Calc to fix it for Account Num,and find the count of charges but i am not getting the right result.

Hey Ombir, I tried both of them, getting the same result, Individual counts for each instance of the charge across the dates

• ###### 5. Re: I am trying to find Accounts which have multiple Charges assigned to them, I tried to creat a row level Calc to fix it for Account Num,and find the count of charges but i am not getting the right result.

Try this. Just keep only Account number and this field in the view.

{FIXED [Acc No]: COUNTD((DATEPART('year', [Date Field])*100 + DATEPART('month', [Date Field])))}

If this doesn't works then please post a workbook with Mock Data.

1 of 1 people found this helpful
• ###### 6. Re: I am trying to find Accounts which have multiple Charges assigned to them, I tried to creat a row level Calc to fix it for Account Num,and find the count of charges but i am not getting the right result.

So that worked on my sample data set, but not the original data. So I created an extract of the piece of data i wanted to work with and i'm blending it with my original data source. And now the above formula works like a charm!! Thanks for the help Ombir!!!