Count based on two columns

[aledc]

ID      Letter
111      A
111      B
111      A
222      B
222      A
333      A
333      A
333      A

I want to create a column that tells you the number of occurrences of A in the ‘Letter’ column for a given ‘ID’. In this example, the count would result in 2 for ID 111, 1 for ID 222 and 3 for ID 333.

I’d want a table like this:

ID      Letter      Count of As
111      A            2
111      B            2
111      A            2
222      B            1
222      A            1
333      A            3
333      A            3
333      A            3

Thanks for your help

[amitchandak · Accepted solution]

@aledc , A new column like

countx(filter(table,[ID] = earlier([ID]) && [Letter] = "A"),[ID])