Count of staff with hours worked between specific values

[NZQA-Cam]

I'm rebuilding a dashboard based on a 'dashboard' built using Excel.

In the original dashboard there is a table with a count of the number of staff who have recorded hours worked totalling between 4 specific ranges. I need to recreate this table below.

 'NAA' is the staff member

Staff ('NAA' in this case) regularly record the number of hours they have worked in various categories, so there are multiple lines of data per user. The column for number of hours worked is a Decimal Number.

I figure I need to calculate the total hours worked per user and then get a count of how many users have recorded hours within each range. I'm just not sure where to start with this.

Any help would be greatly appreciated!

[v-yohua-msft]

Hi, @NZQA-Cam 

First, you need to calculate the total number of hours worked by each user. You can do this by using DAX (Data Analysis Expression) to create a new measure in Power BI. Assuming your table is named, and the column for hours worked is , the measure might look like this:

Total Hours Worked = SUM(WorkHours[HoursWorked])

This measure summarizes all the working time entries for each user.

Next, create measures to calculate the number of employees in each specified range. For example, if one of the ranges is 15-100 hours, you can create a measure like this:

Count 0-20 Hours = CALCULATE(COUNTROWS(WorkHours), [Total Hours Worked] > 15, [Total Hours Worked] <= 100)

Repeat this step for each hour range and adjust the filter conditions accordingly.

Once you've set up a measure, you can easily visualize this data in Power BI.

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.