How to count the occurrences of each value in a column, including repeated values.

[sukantdas]

Hi All,

I am very new to Power BI , and running with an issue. I am sure I will definitely get the solution to my issue here. 

Issue :

The below is a column I have

Col A

1

2

3

1

2

3

4

I need 

Col A          Occurance

1                   2

2                   2

3                   2

1                   2

2                   2

3                   2

4                   1

I want to count the occurrences of each value in a column , including the duplicates. 

Thanks a ton in advance.

Regards,

SKD

[JofrainVisda]

How about this. Count the occurence with parameters? like count the "Name" that is under the same Column 1 and Column 2?

[Eric_Zhang · Accepted solution]

@sukantdas

Add a calculated column.

Occurence = 
COUNTX (
    FILTER ( yourTable, EARLIER ( yourTable[Col A] ) = yourTable[Col A] ),
    yourTable[Col A]
)

[dbest_david]

How can we do this for every related data in another column
ie
col A            colB               Occurence 
Mango         Unripe              2
Orange         Ripe                 1
Mango          Unripe             2 
Orange         Unripe             2
Mango         Ripe                 1
Orange        Unripe              2

So it counts based on two column conditions   

[killian]

Hello, 

How do i make my calculated column to take into consideration the filters of my BI ? 

Thanks in advance,

Killian

[SKey]

Hi all,

Should there not be single quotes surrounding tableand column details? I couldnt get it to work without them.

Occurence = 
COUNTX (
    FILTER ( 'yourTable', EARLIER ( 'yourTable[Col A]' ) = 'yourTable[Col A]' ),
    'yourTable[Col A]'
)

Thanks.

Simon

[Anonymous]

THANK YOU, I have been searching all day and finnally your solution did the job!
@Eric_Zhang 

[nisuomi]

Hello,

Is there any way to do this on the M Query side of things?

Been looking for a solution, but my M Query is not so strong.

Cheers,

Niko

[Helen_R]

Hi there,

I've jus tried using your suggested solution to count the occurence of each userID in a column and have found the output appears to square each result. For example, where a userID appears twice in the column, the output in the occurrence column is 4 and where userID appears 6 times in the column the output in the occurrence column is 36. Any idea why this is happening? I really can't get my head around it I'm afriad!

Thanks for any help you can provide.

[emkane]

@Helen_R  Did you ever find a solution? I am having the same issue. 

[bdmichael09]

In your visual, are you doing a count of the new field or a sum? If you do a sum, it will seem like the output is squared but the reality is you just have to change what the table/matrix is doing to the field. You could also probably do a max or min. Basically, just don't sum the values. 

[anoop_ellickal]

I have a column like this 

mango

orange

mango

grape

orange

mango

orange

mango

apple

need an out put like 

mange 4

orange  3

Grape  1

Apple 1

[sukantdas]

Hi  @Eric_Zhang

 Thanks for the solution . It worked for me.  Can you please suggest where & how to increase knowledge in DAX.

Regards,

SKD