Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

substract sum with single value

Hi all,

 

I'm new with Power BI so please don't shoot me as i'm trying to understand the functions 🙂

 

We are building an Power BI Dashboard where we calculate how many billable hours the employees makes per week.

each employee can have different contract hours per week and different billable targets. the billable target does not change per week, it is fixed for full year.

For example, an employee can have a contract of 40 hours per week but it's target is to be 24 hours billable per week.

The worked hours are extracted from AFAS where each row has an entry. so an employee can work for multiple customers per day and do internal work for example.

 

The billable hours per employee are not specified in the import file so i created a table (employee - targethours) in Power BI to calculate from there.

 

I want to create a Matrix Table where:

Rows = employee (colum in import file)

Colum = week (colum in import file)

value = sum of worked hours - billable hours

 

Problem is that when i use 

MEASURE = CALCULATE ( SUM 'importfile'[entrys]) - SUM ('target'[targethours]))

it gives me a pretty high minus number.

result = -151,25 (looks like the sum of 'target'[targethours] minus worked hours that week )

expected = 34 (worked hours ) - 24 (billable hours) = 10 (employee had 10 billable hours too much that week)

 

I think that the MEASURE i created is almost correct but i shouldn't use the 2nd SUM but i cant find a method how i can do that.

6 Replies

  • Please post the exact measure you are using - looks like yours is missing parentheses.

     

    Assuming you are using a Dates table you may be summing up the same billable hours for each day of the week, thus overstating the value by a factor of 7 (or 6 if you want to be a math stickler).  So you may need to use AVERAGE or MAX instead of SUM.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Is this what you mean?

      Measure = CALCULATE(SUM('importfile'[Aantal])) - CALCULATE(SUM(target[targethours]))

      ( i replaced the original because it contaisn company names)

      • lbendlin's avatar
        lbendlin
        Super User

        Your second CALCULATE likely needs to use AVERAGE, not SUM.

         

        Hard to help without good sample data.

  • Anonymous's avatar
    Anonymous
    Not applicable

    Is there anyone who can help? 🙂 starting to get real desperate now...

    • Icey's avatar
      Icey
      Community Support

      Hi Anonymous ,

       

      The file you shared has expired. Could you share it again?

       

       

      Best Ragards,

      Icey