# Count till current row

### Question

• Hi,

Let's say that in the PowerPivot window, I have a single column of names as

Ashish
Mahesh
Rajesh
Ashish
Ashish
Rajesh
Ashish

I would like to write a calculated column formula which will determine the occurrence of each name from the first entry till the current entry.  Therefore, the result I am expecting is

1
1
1
2
3
2
4

I wrote the following two calculated column formulas but they return the following result

4
1
2
4
4
2
4

`=COUNTAX(filter(Data,Data[names]=EARLIER(Data[Names])),VALUE(Data[Amount]))`

`=COUNTROWS(FILTER(data,Data[names]=EARLIER(Data[Names])))`

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

Saturday, September 07, 2013 3:01 AM

• yes, it could (theoretically) happen, that RAND() generates the same number twice for two different rows - though, I do not think that this would ever happen in practice

the problem is that your requirement and/or data do not really follow any pattern. Usually you would e.g. have any other column to distinguish the different rows for [Names] (in this case 'Ashish')
e.g. an OrderNumber, Date, SurrogateKey, etc. which could replace the artificial RAND()-column
as you do not have any of these (?) it is also hard to bring your rows in any specific order that's why we need to do the workaround using RAND()

-gerhard

- www.pmOne.com -

Tuesday, September 10, 2013 7:22 AM

### All replies

• You can add another condition to your FILTER so that current row is only compared to rows equal to or less than it in the sequence.  You seem to be expecting your list of names to be in some order.  A rownumber or date (with tiebreakers) would work well.

If that doesn't help, post some sample rows from your table to make it more clear what you're working with.

Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

Saturday, September 07, 2013 8:42 PM
• Hi,

Your solution would require me to add another column in my base data with row numbers.  I do not want to add another column in my base data although I am OK with using additional columns (calculated columns) in the Power Query window.

Given the list of names as shown above, I simply want to get the result as 1,1,1,2,3,2,4.

I am OK with getting this result after using additional Pivot Table calculated columns but I do not want to add another column in my base data.

Thank you.

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

Monday, September 09, 2013 12:38 AM
• Hi,

Let me share a better description of my problem.  I have a two column table as follows:

Ashish         101
Mahesh         10
Rajesh          11
Ashish         234
Ashish         234
Rajesh          34
Ashish         101

Now my objective is the sum up the top3 values for each name.  So this is what I did

Step 1: I first wrote a calculated item formula to compute the rank

`=RANKX(filter(data,Data[Names]=EARLIER(Data[Names])),Data[Amount],Data[Amount],0,dense)`

Step 2: I then wrote the following calculated Field formula to sum the top 3 values per name

`=sumx(topn(3,Data,Data[Names]),VALUE(Data[Amount]))`

The result for Ashish 670 (this is the sum of all value of Ashish) whereas it should actually be 569 (234+234+101).

I am getting an incorrect result for Ashish because the rank assigned to various instances of Ashish is 2,1,1,2. Since all these numbers are less than 3, it sums up all the amounts of Ashish.

I am somehow trying to figure out a way to assign different rank values to Ashish even if the amounts are the same.

Hope this is a much better description.

Thank you for your help.

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

Monday, September 09, 2013 1:13 AM
• you could create a calculated column as =RAND() and call it [MyRandColumn]

then you can use this calculated measure to get the TOP3 only:

`Top3:=SUMX(TOPN(3,Data,Data[Names],1,Data[MyRandColumn]),VALUE(Data[Amount]))`

not very elegant but should solve your issue

hth,
gerhard

- www.pmOne.com -

Monday, September 09, 2013 8:45 PM
• Hi,

Thank you for replying.

Taking a cue from your solution, this is what I did.

1. I created a calculated column called random by using =RAND()
2. I then created another calculated column called rank by using

`=RANKX(filter(data,Data[Names]=EARLIER(Data[Names])),Data[Amount]+Data[Random],Data[Amount]+Data[Random],0,dense)`

The second calculated column produced different ranks for same amounts of Ashish

3. To sum the top 3 amounts for each name, I used the following calculated field formula in the PowerPivot

`=sumx(topn(3,FILTER(Data,Data[Rank]<=3),Data[Names]),VALUE(Data[Amount]))`

The problem (I think) is that the random values generated in step 1 may/may not be unique i.e. for both values of Ashish 234, the random values generated by =RAND() could be the same.  If that happens, then the RANK calculated column will generate the same rank for the same amount.

So is there a way to generate unique random numbers in step 1.

Thank you.

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

Tuesday, September 10, 2013 1:52 AM
• yes, it could (theoretically) happen, that RAND() generates the same number twice for two different rows - though, I do not think that this would ever happen in practice

the problem is that your requirement and/or data do not really follow any pattern. Usually you would e.g. have any other column to distinguish the different rows for [Names] (in this case 'Ashish')
e.g. an OrderNumber, Date, SurrogateKey, etc. which could replace the artificial RAND()-column
as you do not have any of these (?) it is also hard to bring your rows in any specific order that's why we need to do the workaround using RAND()

-gerhard

- www.pmOne.com -

Tuesday, September 10, 2013 7:22 AM
• Thank you for reconfirming.

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

Tuesday, September 10, 2013 7:33 AM