substract sum with single value

[Anonymous]

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.

[Anonymous]

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

[Icey]

Hi @Anonymous ,

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

Best Ragards,

Icey

[lbendlin]

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]

Is this what you mean?

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

( i replaced the original because it contaisn company names)

[lbendlin]

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

Hard to help without good sample data.

[Anonymous]

Hi,

I've recreated the file with sample data. Couldnt attach it in a post so uploaded it to WeTransfer https://we.tl/t-5uHyzX6SLm 

I want to have the value of the Mease show = (sum of Aantal) - targethours

so in this case:

Employee 1 worked 41,25 hours that week while target is 8, so + 33,25

	Week 1	Week 2
Employee 1	33,25	42,25
Employee 2	24	25,5
Employee 3	20	20