Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Cumulative Count Column by Group

Hi there!

 

I am trying to implement a column like so:

 

animalcount
cat1
dog1
mouse1
cat2
dog2
mouse2
cat3
mouse3
cat4
dog3
mouse4

 

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.

 

 

 

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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
)

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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
)