7 Replies Latest reply on Oct 4, 2018 8:12 AM by sabari.shrestha.0

# How to return the total Count before the highest number.

Hi, I am trying to count the number of times (row) a person made the donation before they made their highest yet in a year. The last column shows what is the highest amount that person donated in a year so far and the 3rd column shows the donation made by that person so far.

So as an example, if that person has donated \$720 as his highest donation yet in a year so far, I want to return the number (count) of donation he made before he made that (\$720) donation. In this case, I want to return 3. Thank you.

• ###### 1. Re: How to return the total Count before the highest number.

Hi Sabari,

It can be doable with fixed calculation

Is it possible to attach a sample workbook

BR,

NB

• ###### 2. Re: How to return the total Count before the highest number.

Hello,

IYou can try below logic:

{ Fixed [Person_Name],[Year] : COUNTD(IF [DONATION] ={Fixed [Person_Name],[Year] :MAX([DONATION])}  THEN [Transaction_ID] END)

• ###### 3. Re: How to return the total Count before the highest number.

Hi Naveen, I have attached a sample file. Please let me know if that's clear. Thank you

• ###### 4. Re: How to return the total Count before the highest number.

Hi, I tried the solution. But It returned all 1 instead of the total count. I could not figure out where I might be doing wrong.

• ###### 5. Re: How to return the total Count before the highest number.

Hi Shrestha,

Final O/p:

Hope this helps

BR,

NB

1 of 1 people found this helpful
• ###### 6. Re: How to return the total Count before the highest number.

Hello,

You can try below approach:

1.   Max donation = ({Fixed [User ID] :MAX([Donation])} )

2. max donation year = {FIXED [User ID]:max(if [Donation]=[Max donation] then year([Date]) end)}

3. count = {FIXED [User ID]:COUNTD(if YEAR([Date])<[max donation year] then YEAR([Date]) END)}

1 of 1 people found this helpful
• ###### 7. Re: How to return the total Count before the highest number.

Thank you for your help. It worked