Forum Discussion
AshleyJ17
Helper II
Helper IIHow to progressively count values in a column
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 ...
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:
FreemanZ
Super User
Super Userhi 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
