PowerPivot DAX Set the Maximum Value Per Group

Question

I am working on a report and need to report hours per employee. However, some people worked longer than Max Hours and a simple sum will not work in the following case... I tried to use the Min Function but it only works as column level... I saw =calculation function should work but I am not sure how to write it... below is the example:

Staff ID    Date            Work Hours
A001        5-Jan-2015      8           
A001        6-Jan-2015      8
A001        7-Jan-2015      8
A001        8-Jan-2015      8
A001        9-Jan-2015      8
A002        5-Jan-2015      7           
A002        6-Jan-2015      7
A002        7-Jan-2015      6
A002        8-Jan-2015      7
A002        9-Jan-2015      6

Staff ID    Staff Name      Max Hours Per Week
A001        Person A        35
A002        Person B        35


Output:
Staff ID    Hours
A001        35      (instead of 40)
A002        33      (7+7+6+7+6)

Thanks a lot for your help!

Jacob Jacob · Accepted Answer · 2015-01-15T15:55:33

Start with a Measure called hours that simply sums the column:

=SUM(Table1[Work Hours])

This measure then uses that sum and does an IF() to check whether the person is over the limit and returns the appropriate number. The SUMX() iterates over each person in order to give you a correct total.

=
SUMX (
VALUES ( Table1[Staff ID] ),
IF (
    [Hours] > VALUES ( Table2[Max Hours Per Week] ),
    VALUES ( Table2[Max Hours Per Week] ),
    [Hours]
   )
      )

Assumes you have 2 tables called table1 and 2 that are related on Staff ID.

PowerPivot DAX Set the Maximum Value Per Group

1 Answers