IF SUMX / Calculate row total issue

[mvananaken]

Hello Folks,

I got a example table with:

Date               Employee        hours

2016-01-01   Employee A       8

2016-01-01   Employee A       8

I'm looking for a measure where the Sum of hours per date per Employee is max 8 and not 16.

(IF SUM hours per date per employee >=8  if true: 8 if false [hours] )

How to achieve this in DAX?

Help is greatly appreciated, ty!

[v-qiuyu-msft]

Hi @mvananaken,

In your scenario, you can create a measure like below:

Maximum = IF(CALCULATE(MAX('Table'[hours]),ALLEXCEPT('Table','Table'[Employee],'Table'[Date]))>=8,8,MAX('Table'[hours]))

Best Regards,
Qiuyun Yu

[mvananaken]

Hi @v-qiuyu-msft,

Thank you! I really appreciate your help. I tried your solution, but one thing goes wrong in my case: I also want to sum hours per day per employee, with a max of 8 per day per employee:  in your example the  total of rows should be 27 instead of 8 in my case. 

How can we achieve this?

Best Regards,

Marco van Aken

[LaurentCouartou]

Looking for something like this?

SUMX(
    SUMMARIZE( Table, [Date], [Employee] , "TotalHours", SUM(Table[Hours])
    , IF( [TotalHours] >= 8, 8, [TotalHours])
)

[mvananaken]

Hello @LaurentCouartou,

Thank you! I really appreciate your help.I tried this but i got an error: Too few argumtens were passed the sumx function. "Too few arguments were passed to the SUMX function. The minimum argument count for the function is 2" I tried to change some " ( , etc) but i won;t work. Any idea?

See also my reply on Qiuyun: I also want to sum hours per day per employee, with a max of 8 per day per employee:  in his example the  total of rows should be 27.

Best regards,

Marco van Aken

[LaurentCouartou]

You probably missed a coma somewhere. As it says, you need 2 arguments for this function to work.

[Vvelarde · Accepted solution]

@mvananaken

Try with this :

MaximumTotalHours =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', 'Table'[Date], 'Table'[Employee] ),
        "TOTALHOURS", IF (
            CALCULATE ( SUM ( 'Table'[Hours] ) ) >= 8,
            8,
            CALCULATE ( SUM ( 'Table'[Hours] ) )
        )
    ),
    [TOTALHOURS]
)

and for better performance in large datasets, combine both measure

HoursCalc =
IF ( HASONEVALUE ( 'Table'[Date] ), [Maximum], [MaximumTotalHours] 

[mvananaken]

Thanks a lot @Vvelarde! You're the DAX king! Credits en kuddo's!

Best regards, 

Marco

[mpo]

It sounds like you need calculated column(s), not measure.

This should work, though I cannot test now so you may need to change something.

hours total = CALCULATE(SUM(hours)),Table[Employee]=Table[Employee],Table[Date]=Table[Date])

hours per date per employee = MIN( 8 , Table[hours total])

[mvananaken]

Hello @mpo,

Thanks for help! I tried in with a calculated column, but in my case I also want to SUM multiple dates, based on max 8 hours per employee per date. More rows per employee per date is a possible scenario. 

Table

Name                   Date                     Hours                 

Employee A          10-31-2016          8

Employee A          10-31-2016          8

Employee B          10-31-2016          8

etc                                              

Should be in a PB table or matrix:

Name                   Date                     Hours                 

Employee A          10-31-2016          8

Employee B          10-31-2016          8

etc                                                     ------

                                                          16 in stead of 24.

Also sea my earlier replies in this post! 

Thank you very much!

Best regards,

Marco van Aken