How can I count the total number of distinct recipients that worked a total of 0-20 hours in 2015?

[malradi88]

I am trying to say 'count the total number of distinct recipients that worked a total of 0-20 hours in 2015.' The formula I have only counts the total number of rows (in the excel document) that have less than 20 hous (i.e lets say 'Matt' is recorded in 2 rows, with each row having 15 hours...the formula below counts both those lines as if they were distinct people even though they correspond to the same perosn who would have well over 20 hours).

Count (distinct {$<[Total Hours]={"(>=0 <=20)"}, Year={'2015'}>}  Receipient)

I am hoping i am just missing something simple! I have been scouring forums and trying different things. Thank you for your time and consideration.

[Nicole-Smith · Accepted solution]

Try this (you need to sum the number of hours at the recipient level):

count({<Recipient={'=sum({<Year={2015}>}[Total Hours]) <= 20'}>} distinct Recipient)

If, for some reason, you have negatives or need to include the >=0 portion, you could do so like this:

count({<Recipient={'=sum({<Year={2015}>}[Total Hours]) >= 0'}*{'=sum({<Year={2015}>}[Total Hours]) <= 20'}>} distinct Recipient)

[malradi88]

Amazing! Thanks Nicole