How to progressively count values in a column

[AshleyJ17]

Hi All

After some help with the sample data i have below, i have a column called Reference and want to add a new column which counts how many occurences cumulatively within the data, for example '1472321-2860846' shows up 3 times in the reference column so what i would like is for Power Bi to show that it occurs 3 times but show the 1st instance, 2nd Instance and 3rd Instance if that makes sense, have done a bit of googling and searching this forum but so far havent found a solution 

Hoping this communintiy can help

Thanks in advanced

Ash

[FreemanZ · Accepted solution]

hi @AshleyJ17 

try to

1) add an index column and make it like:

https://learn.microsoft.com/en-us/power-query/add-index-column

2) add a calculated column like:

CumCount = 
COUNTROWS(
    FILTER(
        data,
        data[Reference]=EARLIER(data[Reference])
            &&data[Index]<=EARLIER(data[Index])
    )
)

it worked like:

[AshleyJ17]

That worked like a charm thank you FreemanZ 🙂

If you have the time would you be able to explain how this works, im not too familiar with the EARLIER function and would like to get some more understanding on the functions in the formula