Count if hours = less than

[rdops]

Hi all, mee again needing some help.

I am doing a team capacity report and would like to show which roles have more available hours in a period. Say, 

If role name = Project Manager and the hours booked on a person who is a project manager is less than 70 (per month) than count.

I also wanted to show people on a specific role that have zero hours allocated on a month and again display them by skills. E.g

Mary and John are both Project managers and they both have zero hours booked in June. Paul, Ani and Trish are Copywriters and they all have zero hours booked. I want a report that tells me tha I have 2 Project managers and 3 Copywriters completely free. Then I want to be able to click on these roles and see who are the people within those roles who are completely free (or half allocated).

Hope it makes sense and hope you can hellp me 🙂 

[v-juanli-msft]

Hi @rdops 

For example, you have tables below:

please create measures

Measure = CALCULATE(SUM('Table'[hours]),FILTER(ALLSELECTED('Date'),'Date'[year-month]=MAX('Date'[year-month])),VALUES('Table 2'[id]))

Measure 2 = CALCULATE(DISTINCTCOUNT('Table 2'[id]),FILTER('Table',[Measure]<=70))

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rdops]

Thanks for that, but how do you calculate if all the fields are on the same table? 

[v-juanli-msft · Accepted solution]

Hi @rdops 

If all columns are in one table,

It is better to create a date table and relate the date with your main table.

Measure = CALCULATE(SUM('Table'[hours]),FILTER(ALLSELECTED('Date'),'Date'[year-month]=MAX('Date'[year-month])),VALUES('Table'[id]))

Measure 2 = CALCULATE(DISTINCTCOUNT('Table'[id]),FILTER('Table',[Measure]<=70))

Best Regards

Maggie