Count Occurance in Column

[James02]

Hi,

I hope this calculation is easy to figure out. I want to write a calculated column or measure to count the number of times a value occurs in another column.

Column #1
Joe
Joe
Joe
John
Alex

So I would want Column #2 to be:
3 (for three joes)
3 (for three joes)
3 (for three joes)
1 (for one john)
1 (for one alex)

Is this simple calculation I'm missing? Thank you!

 

[AlanY]

try this

=COUNTIF(A:A,A1)

 

[James02]

That formula requires R1C1 which PowerPivot does not use. It would look like = COUNTIF('Table'[Name],'Table'[Name]). But that returns an error.

 

[James02]

I found the solution in another thread in this forum. For a Excel formula such as =COUNTIF(A:A,A1).

PowerPivot Equivalent is:

=CALCULATE(COUNTROWS(TableName), ALLEXCEPT(TableName, TableName[Items]))

 

[Kazlik]

You can use just COUNTROWS in your measure and then let the table to do rest with. Putting Column1 in rows will get you the counts you want.

Count:=COUNTROWS(TableName)

 

[James02]

You can use just COUNTROWS in your measure and then let the table to do rest with. Putting Column1 in rows will get you the counts you want.

Count:=COUNTROWS(TableName)

Are you sure? This gives me the a value of "1".

 

[PentaGalCXO]

My preference for Column2 in Table1:

=calculate(countrows(Table1),filter(Table1,[Column1]=earlier([Column1]))