cancel
Showing results for
Did you mean:
Helper II

## Measure Help - Average Hours a Week Grouped into Buckets

I have the following table with the following columns

Time:

Emp ID | Date |  Week | # of Hours Charged

Data goes back 10 weeks

Week =Time[Date] - weekday(Time[Date],2) +1

Ive been asked to create a dynamic filter that would bucket the average number of hours someone charges by week:

Group 1 = Average 40 hours in a week

Group 2 = average Charging > 30 hours and <= 40 hours

Group 3 = average >20 hours && <=30 hours

Group 4 = average >10 hours && <= 20 hours

Group 5 = average <=10 hours

Currently the data is being displayed as follows:

Emp ID | Week 1 | Week 2 ...... Week 10

Emp A | 40 | 35 | 25 ... 10 - Averaging 35 hours a week

Emp B | 10 | 30 | 45 ... 50 - Averaging 30 hours a week

Does anyone know how to write this in DAX?

3 REPLIES 3
Highlighted
Helper IV

## Re: Measure Help - Average Hours a Week Grouped into Buckets

Below is a formula I used for my own buckets. I think if you adapt it to your data you should get what you need. Before you use it though just create a measure that takes the average of the hours charged and use that as the field being put into buckets. Let me know if this helps!

Usage_Buckets = if(YRMO_USAGE[BI_USAGE] <= 20000, "10,000 - 20,000", if(YRMO_USAGE[BI_USAGE] <= 40000, "20,001 - 40,000", if( YRMO_USAGE[BI_USAGE] <= 60000, "40,001 - 60,000", "60,000+")))
Community Support

## Re: Measure Help - Average Hours a Week Grouped into Buckets

I solved the similar case here: https://community.powerbi.com/t5/Desktop/Chart-based-on-measure-buckets/td-p/795974

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

## Re: Measure Help - Average Hours a Week Grouped into Buckets

All of this makes sense with the buckets, but how do I make sure those buckets are calculating the weekly average hours? What calculation do I use to make sure the average measure is the average hours in a week (not a day, or by row in the table).

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors