sum not working correctly - maybe need selected value

[maurcoll]

Hi

I am trying to calculate the proportion of hours when we have to move people to a different store/dept due to staffing levels.
I have the number of hours that they were moved for and the total hours worked.
However when i do a sum of total hours worked it is giving me the total hours for everyone that has worked on that day not just the ones that were moved. In the below example it is giving me 1360 as total hours worked when it should be 69.5. I am only using Sum(hours worked) as the calculation, if i put into the filter total hours worked is not blank this works but i cant use this in a card visual ideally i want to do this in a measure if possible, not sure if it needs a selectedvalue or allselected.

Date	Team	Hours Moved Location	Total Hours Worked
13/05/2024	1	35.15	51.5
13/05/2024	2	31.5	18
Total		66.65	1360
		should be	69.5

[Anonymous]

Hi @maurcoll ,
Thanks for @DataInsights  reply.
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

Create a measure

Total Hours Worked = 
CALCULATE(
    SUM('Table'[Hours Worked]),
    FILTER(
        'Table',
        'Table'[Hours Moved Location] <> BLANK()
    )
)

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[DataInsights]

@maurcoll,

One idea is to check the Hours Moved Location value: if it has a value then sum Hours Worked:

Total Hours Worked =
IF ( NOT ISBLANK ( [Hours Moved Location] ), SUM ( Table[Hours Worked] ) )

[DataInsights · Accepted solution]

Actually, wrap this expression in SUMX. Does your model have a date table? If not, it's best practice to create a date table. In this example, the date table is 'Date' and has a relationship with the data (fact) table. The date field in the visual should be from the Date table.

Total Hours Worked =
SUMX (
    VALUES ( 'Date'[Date] ),
    IF ( NOT ISBLANK ( [Hours Moved Location] ), SUM ( Table[Hours Worked] ) )
)