Cumulative Count Column by Group

[Anonymous]

Hi there!

I am trying to implement a column like so:

animal	count
cat	1
dog	1
mouse	1
cat	2
dog	2
mouse	2
cat	3
mouse	3
cat	4
dog	3
mouse	4

Where for each instance of animal, a count is incremented. Essentially, it is 3 independant counters, each counting the occurance of that animal.

Any thoughts?

Thanks!

Im trying something like this at the moment:

Count =

VAR countCat = CALCULATE(RANKX('Table','Table'[Animal],,ASC,Dense), FILTER('Table','Table'[Animal]="Cat"))

return countCat 

I will have the other animals as VARs as well and an if statement in my return statement to assign values for each row.

[Zubair_Muhammad · Accepted solution]

@Anonymous

Hi

First add an Index Column from the Query Editor

Then you can RANK based on that Index to get the desired calcualted column

i.e

Count =
RANKX (
    FILTER ( Table1, Table1[animal] = EARLIER ( Table1[animal] ) ),
    [Index],
    ,
    ASC
)

[Zubair_Muhammad]

@Anonymous

File attached as well