Measure not totalling correctly

[ericsara]

Hi team,

I know you may need more details than what I add here but I know this is a common issue but I cannot work out how to get the right result. 

I have this measure. 

Std Hours Last Week of Selected Week =

VAR _OneWeekEndSelected = HASONEVALUE(Dates[WeekEnding])

VAR _SelectedWeekOffset = IF(_OneWeekEndSelected=TRUE,VALUES(Dates[WeekOffset]),99)

VAR _WeekOffsetNeeded = _SelectedWeekOffset -1

VAR _HoursWorkedTotal = CALCULATE(SUM('Star Projects Timesheet Data'[Hours]),ALL(Dates),Dates[WeekOffset]=_WeekOffsetNeeded)

VAR _StdHours = CALCULATE(SUM('Std Hours (2)'[Std Hours]),ALL(Dates),Dates[WeekOffset]=_WeekOffsetNeeded)

VAR _Include = IF(_HoursWorkedTotal=0,FALSE(),TRUE())

VAR _IsContractor = IF(Max(Resources[Type])=1,TRUE(),FALSE())

VAR _ContractorHours = IF(_Include=TRUE() && _IsContractor=TRUE(),_HoursWorkedTotal,0)

VAR _NonContractor = IF(_Include=TRUE() && _IsContractor=FALSE(),_StdHours,0)

VAR _TotalHours = _ContractorHours + _NonContractor

return _TotalHours

And I get this result

The value for Alan and Ellie is correct but the total is not. 38+6=44 not 44.75.

Can anyone see why?

Thanks,

[amitchandak]

@ericsara , Change return like

return
sumx(Values('Star Projects Timesheet Data'[Staff Name]), calculate(_TotalHours))

[ericsara]

@Amit - Thanks for that. 

I now get this.

[v-cgao-msft]

Hi @ericsara ,

Are there any other fields in the table visual other than [Staff Name]? You need a virtual table to fill in the missing filters(fields). Try like:

Measure = 
SUMX(
    SUMMARIZE(
        'Table',
        'Table'[Column1],
        'Table'[Column2],
        'Table'[Staff Name],
        "std hours",
        [Std Hours Last Week of Selected Week]
    ),
    [Std Hours Last Week of Selected Week]
)

'Table'[Column1]'Table'[Column2] and so on are all the fields in the table visual.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data