Total hours per employee, per week

[gandalfuy]

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!

[v-juanli-msft · Accepted solution]

Hi @gandalfuy

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

[ElliotP]

@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.

[gandalfuy]

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!