How to make a correct total when I have assigned a different value to negative values

[Kassie72]

Hi,

I have created a column 'Available hours' for my employees. However due to some circumstances (someone has taken vacation, while being sick at the same time) I get a negative value for one of the staff members:

I would like to obtain the result in the last column. So I have created the following measure:

if ([Available Hours] > 0, [Available Hours], (BLANK()))

However when I apply this measure, then I will get the field displayed correctly on row level, but with 68 as the total number of hours. I would like to have 108 as the answer.

What to do?

[Kassie72]

Thank you for your quick answer. I realize that I have not been particular enough.

Available hours is a result of multiple measures.
[Capacity] - [Vacation] - [Sick days] = Available Hours
Vacation and Sick days originate from the same table, Capacity is in a separate table.

When I apply if ([Available Hours] > 0, [Available Hours], (BLANK())) then the correct row result is displayed. However the total Available Hours still takes into account the negative value of -40. 

How can I avoid this?

[Ashish_Mathur]

Hi,

Try this measure

Measure = SUMX(values('Employee'[Employee]),[available hours])

Hope this helps.

[Deku]

You need to use SUMX to interate each row like you have in your table, calcualting [Available Hours] for each row. You can perform the if > 0 within the iteration to exclude the negative numbers

[Deku · Accepted solution]

Sumx(

Values(table[employee]),

If( table[available hours] >0, table[available hours] )

)