none
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])))

    Please help.

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

    Saturday, September 07, 2013 3:01 AM

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 -

    Tuesday, September 10, 2013 7:22 AM
    Answerer

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
    Answerer
  • 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
    Answerer
  • 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
    Answerer
  • Thank you for reconfirming.

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

    Tuesday, September 10, 2013 7:33 AM