4 Replies Latest reply on Oct 1, 2019 3:04 PM by Sheetal Chima

# lookup first value in a group

I have data that looks like this:

GroupA      GroupB      Usercount   Crossover %

abc            abc             100                 100%

abc            def               50                   50%

abc            ghi               40                    40%

def             def             50                    100%

def             ghi             20                      40%

The crossover should be calculated like this -- 100/100

50/100

40/100

Then it needs to restart when the GroupA changes - so

50/50

20/50

I am using the calculated field for crossover% as

Sum([usercount])/LOOKUP(SUM([usercount]), FIRST())

But this is basically incorrect, because when the group A changes, it is still taking the denominator as the 100 (the very first row) - so 50/100, and 20/100.

How can this be solved?

• ###### 1. Re: lookup first value in a group

you need to reset the start point

see an example belwo Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: lookup first value in a group

I tried this but what it's doing is showing 100% for Group B in the 3 row example, and also computing the prior 2 based on the 100 from GROUP A

abc            abc             100                 100%

abc            def               50                   50%

abc            ghi               40                    40%

def             def             50                    50%

def             ghi             20                      20%

def             abc             10                     100%   <----

• ###### 3. Re: lookup first value in a group

Hi Sheetal,

You need this: Thanks

Deepak

If it Helps, Pl Mark it Helpful and CORRECT to Close Thread

• ###### 4. Re: lookup first value in a group

I had to use the Sort option in this in addition to what you provided to make it work correctly. thanks!