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

     

DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
2 years ago

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's avatar
DataInsights
Icon for Super User rankSuper User
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] ) )
)