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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
rajendraongole1
Super User
1 year ago

Hi Anonymous -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
 
 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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. 

    • rajendraongole1's avatar
      rajendraongole1
      Super User
      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        rajendraongole1 this is perfect, thank you!

    • ArwaAldoud's avatar
      ArwaAldoud
      Super User
      1 year ago

      Hi Anonymous 

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

      Thanks for sharing your update.