Create new column which contains count of occurrences of already existing column

[vemee]

Hi,

I have a column A, which contains colors.

Column A:

Blue

Red
Green
Green
Blue
Blue

I would like to create a column B which counts how many time the value in a row appears in column A, and adds this number to the same row in column B.

In this case, column B would be as following:

Column B:

3 (since

1

2

2

3

3

Is this possible to solve? Feels impossible, but I knwo there are many smart people out there, so I hope som brilliant mind can solve it 🙂

Best, Ville

[Musadev · Accepted solution]

@vemee 
Create a calculated column with the same script and you will be able to get the same result and then you add it to the char. check the output here. CC Count is a column.

[Musadev]

Hi @vemee 

On the visual, the records are clubed and it shows a single unique value for duplicates. so I added an index to the data and the results are below.

[vemee]

Hi,

Thanks! However, I would like to have the count of each category (blue, red and so on) on the x-axis. And it is not possible to have a measure on the x-axis on a column chart. Therefore it would need to be a column in the table.

[Musadev · Accepted solution]

@vemee 
Create a calculated column with the same script and you will be able to get the same result and then you add it to the char. check the output here. CC Count is a column.

[vemee]

Hi,

It worked!! Did not know that you could make a calculated column, thanks for sharing 🙂

Could I ask for one more thing. Is it possitble to also make a column which groups the values in my calculated column?

Now (in my real data) I have those that only contain 1, 2, 3, 4... and so on until 100+, is there a way to group them by saing if value is between 0-10, then output 1-10, if value is between 11-20 then output 11-20?

Best, Ville

[Musadev · Accepted solution]

@vemee 
Yes, you can label each row based on the count, 
Create a new column and add this script to it. and see the results

Label = SWITCH( TRUE(), 'Table'[Count] <= 10, "1-10", 'Table'[Count] <= 20, "11-20", 'Table'[Count] <= 30, "21-30", 'Table'[Count] <= 40, "31-40", 'Table'[Count] <= 50, "41-50", 'Table'[Count] <= 60, "51-60", 'Table'[Count] <= 70, "61-70", 'Table'[Count] <= 80, "71-80", 'Table'[Count] <= 90, "81-90", 'Table'[Count] <= 100, "91-100", 'Table'[Count] > 100, "100+" )

If your issue has been resolved then mark this as a Solution. New Members will find the solution with the first click. 

[Musadev]

I assume you will have the Primary key in the tables and this Visual as well. 
Try this measure for your data.

tbl3 Count = CALCULATE( COUNTROWS(TBL3),ALLEXCEPT(TBL3, TBL3[Color]))

 
TBL3 is my table and Color the name of the Column. 

[Jonvoge]

Hi vemme

Is there a special reason for doing this as a Calculated Column and not a Calculated Measure?

If you create a Calculated Measure like:

CountItems = Countrows(Table1)

And put it into a table visualisation together with Column 1, it should give you the correct result:

_____________________________________________________
I hope my comment was helpful.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.
Find me on LinkedIn, Sessionize, or my blog Downhill Data

[vemee]

Hi,

Thank you. Not really since I want to plot column B against another measure. So I would actually need to create column B first.