Need a to add a column for summarizing the data based on multiple filter similar to pivot

[AS123]

So I am trying to create another column where its summarising data like a pivot. Current data is as below:

Fruit	User
Pear	User 1
Pear	User 2
Pear	User 1
Apple	User 3
Apple	User 3

What I would like to show is how many users ate the fruit. I want to be able to do that by adding a new column. Any recommendations

Fruit	User	# of users
Pear	User 1	3
Pear	User 2	3
Pear	User 3	3
Apple	User 4	2
Apple	User 5	2

[AS123]

Thank you, that worked like magic!

[Fowmy · Accepted solution]

@AS123 

Add a column as follows:

New Col = CALCULATE( COUNTROWS( table17 ) , ALLEXCEPT( Table17,Table17[Fruit] ) )

How it works:

1. COUNTROWS( Table17 ): This part of the formula calculates the total number of rows in Table17.

2. ALLEXCEPT( Table17, Table17[Fruit] ): This function is used to remove all filters from Table17 except for the ones applied to the 'Fruit' column. It retains only the filters related to the 'Fruit' column.

3. CALCULATE( ..., ALLEXCEPT( Table17, Table17[Fruit] ) ): This function modifies the context in which the count of rows is calculated. It instructs DAX to count the rows of Table17 considering all filters except for those on the 'Fruit' column.