Forum Discussion
How 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 '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
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:
2 Replies
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:
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
