Column Sum * Fixed Number * Column Value

[forti4040]

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 be $1,000 (fake number for this example). 

My approach has been:  Cost of Labor = ( $XX / Sum(Hours Worked) ) * Hours Worked

This isn't working as I believe my mixing of measures and columns is not producing the results I'm expecting.  

Employee	Hours Worked	Cost of Labor
Bob	10	??
Fred	15	??
Alice	20	??
Total	45	$1,000

I can, and have been, hardcoding the total cost of labor into my formula, but I can't figure out how to solve for the other ??'s shown above. Any help would be appreciated. 

[ArwaAldoud]

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.

[danextian]

Hi @forti4040 

Can you please post your actual data, formula and your expected result?

[forti4040]

@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

[rajendraongole1]

Hi @forti4040  - You're right that mixing measures and calculated columns can cause issues. The best approach depends on whether you want this calculation to be dynamic.If you want the cost of labor to update dynamically based on filters or slicers, use a measure instead of a calculated column.

Cost of Labor =
VAR TotalHours = SUM('Employee Data'[Hours Worked])
VAR TotalCost = 1000 -- Replace with your actual total labor cost

RETURN
DIVIDE(TotalCost, TotalHours, 0) * SUM('Employee Data'[Hours Worked])

Check this and let know.

[forti4040]

@rajendraongole1 thank you for this forumla. I entered as you stated, then added Cost of Labor as a column in my table. Unfortunately I get the same value on every row of the table, which is equal to VAR TotalCost. Any thoughts on why? I tried creating this as both a measure and a column just in case I misunderstood and both produce the same result. 

I tried modifying  your formula to remove the final SUM from the hours worked, and that does produce unique values on each row when entered as a column, but the values aren't correct mathematically. 

[rajendraongole1]

Hi @forti4040 -create a calculated column as below:

Cost of Labor =

VAR TotalHours = CALCULATE(SUM('Table'[Hours Worked]), ALL('Table'))

VAR TotalCost = 1000  -- Replace with actual total labor cost

VAR HourlyRate = TotalCost / TotalHours

RETURN 'Table'[Hours Worked] * HourlyRate

[forti4040]

@rajendraongole1  & @ArwaAldoud  both of your solutions look like they work! Initially it didn't look correct because I had filters applied and the numbers were off as a result. 

That is my next question though. I need to filter the page with a few slicers and have the TotalCost, TotalHours, etc... all filtered by the same slicers. Is this possible?

Right now what's happening is that the $1,000 total cost is being divided by ALL values, even if I've filtered the view. I'd like the $1,000 to be divided by what's shown on the screen. 

[ArwaAldoud]

Hi @forti4040 

 I'm happy to hear your issue was solved. Glad my solution helped as well.

Thanks for sharing your update.

[rajendraongole1 · Accepted solution]

Hi @forti4040 - 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.

[forti4040]

@rajendraongole1 this is perfect, thank you!