cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Total hours per employee, per week

I have the following table:

Client | Hours | Month

A | 2 | April

B | 3 | April

B | 1 | April

A | 1 | May

A | 2 | May

B | 1 | May

B | 3 | May

Client A should have a maximum hours per month of 2 and Client B should have a maximum hours per month of 3.

So I want to filter each client that exceeded the maximum hours per month.

For April should appear in a combo box: only B. And for May should appear A and B.

How can I do that? Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

I test with measures as follows

Measure 1 = CALCULATE(SUM(Sheet1[Hours]),ALLEXCEPT(Sheet1,Sheet1[Month],Sheet1[Client]))
Measure 2 = IF((MAX([Client])="A"&&([Measure 1])>2)||(MAX([Client])="B"&&[Measure 1]>3),1,0)

Then add filters to visual by letting Measure 2 equal to 1.

Best Regards
Maggie
3 REPLIES 3
Highlighted
Regular Visitor

I have the following table:

Client | Hours | Month

A | 1 | April

B | 2 | April

A | 2 | April

A | 2 | May

B | 2 | May

A | 1 | May

B | 2 | May

Client A should have a maximum hours per month of 2 and Client B should have a maximum hours per month of 3.

I need a filter which shows each Client that reached the maximum hours per month. In the case of April should appear A. And in the case of May should appear A and B.

How can I do that?. Thanks!

Highlighted
Post Prodigy

@gandalfuyAssuming you have a date table, the easiest solution would be to create a measure which sums the hours column and then place that measure in the 'visual' or 'page' filter of your report adjusting for your requirements.

That's a very basic solution, you might need something more long term/re-usable. A pbix with a richer description/use case would be pretty handy.

Highlighted
Community Support

I test with measures as follows

Measure 1 = CALCULATE(SUM(Sheet1[Hours]),ALLEXCEPT(Sheet1,Sheet1[Month],Sheet1[Client]))
Measure 2 = IF((MAX([Client])="A"&&([Measure 1])>2)||(MAX([Client])="B"&&[Measure 1]>3),1,0)

Then add filters to visual by letting Measure 2 equal to 1.

Best Regards
Maggie

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors