"Count If" at an aggregated level

[AP-BOB]

Hello,

I am trying create a dashboard that identifies how many employees are entering below 40 hours per week. I need to then display by percentage how many employees are on or over 40 hours, and how many are below.

I am new to DAX and really struggling with the syntax especially when the data is at the day/task level.

Sample data below:

ID	Timekeeper Name	Task	Date	Hours worked
1	John Smith	Meetings	1/7/2019	3
1	John Smith	Development	1/7/2019	4
1	John Smith	Meetings	1/8/2019	5
1	John Smith	Development	1/8/2019	3
1	John Smith	Meetings	1/9/2019	2
1	John Smith	Development	1/9/2019	8
1	John Smith	Meetings	1/10/2019	8
1	John Smith	Meetings	1/11/2019	7
1	John Smith	Meetings	1/14/2019	6
1	John Smith	Meetings	1/15/2019	8
1	John Smith	Meetings	1/16/2019	8
1	John Smith	Meetings	1/17/2019	8
1	John Smith	Meetings	1/18/2019	9
2	Jane Doe	Analysis	1/7/2019	3
2	Jane Doe	Meetings	1/7/2019	7
2	Jane Doe	Analysis	1/8/2019	2
2	Jane Doe	Meetings	1/8/2019	4
2	Jane Doe	Analysis	1/9/2019	4
2	Jane Doe	Meetings	1/9/2019	4
2	Jane Doe	PTO	1/10/2019	8
2	Jane Doe	Analysis	1/11/2019	6
2	Jane Doe	Analysis	1/14/2019	11
2	Jane Doe	Analysis	1/15/2019	8
2	Jane Doe	Analysis	1/16/2019	7
2	Jane Doe	Analysis	1/17/2019	8
2	Jane Doe	Analysis	1/18/2019	8

Thank you.

[PANDAmonium · Accepted solution]

First you have to group by week. So in Power Query:

- Create a week column with a function like "= Table.AddColumn(#"Changed Type", "Custom", each Date.WeekOfYear([Date]))"

- Group By Week and Time Keeper Name with an aggregation for Sum of Hours Worked

And then it's just a matter of creating a measure to calculate percentages so count of hours gte 40 divided by count of all so:

Percentage = CALCULATE(COUNT('HRS'[Timekeeper Name]),HRS[Count]>=40) / COUNT(HRS[Timekeeper Name])

[AP-BOB]

Thanks for the info. When you say "- Group By Week and Time Keeper Name with an aggregation for Sum of Hours Worked", do I do that by creating a new table or somehow at the measure level?

[PANDAmonium]

You're transforming the hours worked table, so in Power Query > Transform > Group By > Advanced

[AP-BOB]

Just a quick follow-up question. I am assuming this method will replace my current table? If so, I still need the granular details for other visualization. Do I just create another table?

[PANDAmonium]

Not necessarily. Either duplicate or reference the original table first.

[AP-BOB]

Worked wonderfully. Thank you!