Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Column Sum * Fixed Number * Column Value

Hello, I have a column of hours worked last year per employee and am trying to create a column for associated costs for those hours worked. I know my total spend on employee wages for last year to b...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi Anonymous - Yes! You can make sure the Total Cost, Total Hours, and Cost of Labor all adjust dynamically to filters by modifying the formulas. Right now, your formula uses ALL('Employee Data')

     

    Cost of Labor1 =
    VAR TotalHours = CALCULATE(SUM('Table'[Hours Worked]), ALLSELECTED('Table')) -- Respects slicers
    VAR TotalCost = 1000  -- Adjust this if needed to be dynamic
    VAR HourlyRate = DIVIDE(TotalCost, TotalHours, 0)

    RETURN SUMX('Table', 'Table'[Hours Worked] * HourlyRate)

     

     

     

    Please find the attached pbix file. Hope this helps.

ArwaAldoud's avatar
ArwaAldoud
Super User
1 year ago

You need to ensure the total cost is treated as a constant while dividing it by the total hours worked

TotalCost = 1000 // Replace with actual total spend

Cost of Labor =
VAR TotalHours = SUMX(ALL('Table'), 'Table'[Hours Worked])
RETURN
[TotalCost] / TotalHours * 'Table'[Hours Worked]


If this response was helpful, please accept it as a solution or give kudos to help other community members.