Answered by:
Count till current row

Hi,
Let's say that in the PowerPivot window, I have a single column of names as
Ashish
Mahesh
Rajesh
Ashish
Ashish
Rajesh
AshishI 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
4I 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])))
Please help.
Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com
Question
Answers

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 
 Proposed as answer by Ed Price  MSFTMicrosoft employee, Owner Wednesday, September 11, 2013 6:16 PM
 Marked as answer by Elvis LongMicrosoft contingent staff, Moderator Monday, September 16, 2013 1:16 PM
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

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

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 101Now 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

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 
 Proposed as answer by Ed Price  MSFTMicrosoft employee, Owner Wednesday, September 11, 2013 6:16 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
 Edited by Ashish Mathur Tuesday, September 10, 2013 2:35 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 
 Proposed as answer by Ed Price  MSFTMicrosoft employee, Owner Wednesday, September 11, 2013 6:16 PM
 Marked as answer by Elvis LongMicrosoft contingent staff, Moderator Monday, September 16, 2013 1:16 PM
