Forum Discussion

maurcoll's avatar
maurcoll
Icon for Helper IV rankHelper IV
2 years ago
Solved

sum not working correctly - maybe need selected value

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 h...
Need Help
  • DataInsights's avatar
    DataInsights
    2 years ago

    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] ) )
)

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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