topic Re: How to progressively count values in a column in DAX Commands and Tips

How to progressively count values in a column

AshleyJ17 — Thu, 11 May 2023 11:23:00 GMT

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

Re: How to progressively count values in a column

FreemanZ — Thu, 11 May 2023 13:13:58 GMT

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:

Re: How to progressively count values in a column

AshleyJ17 — Thu, 11 May 2023 13:38:13 GMT

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