Forum Discussion

mvananaken's avatar
mvananaken
Icon for Helper II rankHelper II
9 years ago
Solved

IF SUMX / Calculate row total issue

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...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    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]
LaurentCouartou's avatar
LaurentCouartou
Icon for Solution Supplier rankSolution Supplier
9 years ago
You probably missed a coma somewhere. As it says, you need 2 arguments for this function to work.
Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
9 years ago

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's avatar
    mvananaken
    Icon for Helper II rankHelper II
    9 years ago

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

     

    Best regards, 

     

    Marco