Subtract a value when it has a value otherwise not

[Anonymous]

Dear Community,

I have a table with column Employeenumber, QuantityofHourspayed. But i want to calculate when a person reaches the 1000 hours. If they reach these 1000 hours they get promotion. But the problem is in the data there are also employees that did not work at all. So if you look here below at employeeNumber 5136 is shows blank, automatically it also shows in the other column 1000. Firstly, i do not want to show this employee in my table/visual. Is it possible to not show this value by using DAX? I already know you can select is not empty in the filter.

Secondly what I did in the measure was    Remaining hours for promotion = 1000 - Quantity of hours payed. Should I add an extra column where every row is 1000? I do not want to show these 1000 if quantity of hours.

[Anonymous]

I think the column name should read: QuantityOfHoursPaid.

[harshnathani · Accepted solution]

Hi @Anonymous ,

This will show a BLANK() incase the employee has not worked at all.

 Remaining hours for promotion = IF (Table[Quantity of hours payed] <> BLANK(),  1000 - Table [Quantity of hours payed] , BLANK())

Adding a 1000 column adds to your memory consumption. If all employees are promoted at 1000 hours then I do not think you need an additional column. This will limit it to this usecase, What if your organization plans to promote employees in some specific category at 800 hrs. So it all depends on the usecase.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Thank you for the solution. The only problem is when you look below the table it does not show the right sum of the column. But I got it!