Forum Discussion
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...
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 slicersVAR TotalCost = 1000 -- Adjust this if needed to be dynamicVAR HourlyRate = DIVIDE(TotalCost, TotalHours, 0)
RETURN SUMX('Table', 'Table'[Hours Worked] * HourlyRate)Please find the attached pbix file. Hope this helps.
Hi Anonymous
Can you please post your actual data, formula and your expected result?
danextian since my data is proprietary I can't load it here. The example data is relatively close to what I'm working with.
As far as expected results:
$1,000 / 45 = $22.22/hr. Then I multiply this cost by the number of hours worked on each row to get the below.Employee Hours Worked Cost of Labor Bob
10 $222.22 Fred 15 $333.33 Alice 20 $444.44 Total 45 $1,000