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

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

sukantdas — Thu, 30 Mar 2017 10:21:36 GMT

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

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

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

Eric_Zhang — Fri, 31 Mar 2017 09:44:50 GMT

@sukantdas

Add a calculated column.

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

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

sukantdas — Fri, 31 Mar 2017 10:10:53 GMT

Hi @Eric_Zhang

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

Regards,

SKD

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

anoop_ellickal — Thu, 07 Jun 2018 14:40:25 GMT

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

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

Helen_R — Fri, 03 Aug 2018 12:59:03 GMT

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.

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

nisuomi — Thu, 29 Nov 2018 07:15:28 GMT

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

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

Anonymous — Fri, 07 Jun 2019 21:29:16 GMT

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

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

emkane — Mon, 03 Feb 2020 22:02:14 GMT

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

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

Anonymous — Mon, 06 Jul 2020 13:44:37 GMT

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.

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

SKey — Mon, 21 Jun 2021 13:59:35 GMT

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

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

killian — Tue, 21 Feb 2023 15:35:52 GMT

Hello,

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

Thanks in advance,

Killian

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

dbest_david — Sun, 26 Mar 2023 22:10:43 GMT

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

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

JofrainVisda — Tue, 13 Feb 2024 12:18:20 GMT

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