Counting text occurrences in a colum with in the same table

[mamoormasoomi]

Hi,

I have been trying to get the result  like the below first table in Countkey field. So far I am only able to get the total no. in the count key firled instead of getting the series of # upon the occrance of similar text in Key 1 field. Please check the below 2nd screenshot for the  formula ( Countkey = CALCULATE (COUNT(SHEE1[KEY 1]))  and result so far getting from power bi.

Required Result Table

Key 1	Countkey
AB	1
AC	1
AD	1
AE	1
AE	2
AF	1
AG	1
AG	2
AG	3
AG	4
AH	1
AH	2
AI	1

Getting so far from Power BI.

[vanessafvg]

@mamoormasoomi

are you wanting a cummulative total?

how do you want it ordered? if you dont have a column to order it, you can create an index column in power query

cummulativecount = CALCULATE(countrows(Table1), FILTER(all(Table[Index]), Table1[Index] <= MAX(Table[Index])))

if you do that something like this should work

[mamoormasoomi]

Thanks for your reply but I am still not getting the result the desired result.

I tried to used your provided code and added the index column too but the result is coming up like the below. The first repeated word "AE" is coming three times so when the first time AE comes then the corresponding column (Cummulativecount) should say value "1" then when the point move to the next row it should say "2"  same action should be done with word 'AG" that came 4 times in the column.

I want the the way like the below screenshot where the color highlighted areas are showing the occurence of similar texts with corresponding values in the Countkey field.

[Eric_Zhang · Accepted solution]

@mamoormasoomi

Have you ever tried my solution in your previous thread? Besides that solution(in a attached pbix), you can also try

Result Key_ = RANKX(FILTER('Table','Table'[Key1]=EARLIER('Table'[Key1])),'Table'[Index],,ASC,Dense)