Help with metric Aggr() Line Chartt

[Patrick_Pescetto]

Hello

I am developing a line chart that has a metric with a condition. The metric would be the following:

IF(
SUM(AGGR( SUM(Hours) ), [Name Employee])) <0, 0,
SUM(AGGR( SUM(Hours) ), [Name Employee]))
)

What I want is for the hours to only be counted and added if they are positive, but it has to be evaluated per employee.

Currently I put this metric but it does not work globally. If I choose an employee with negative hours it works, but if I select 3 employees with positive hours and 1 with negative hours, it is not able to exclude the employee with negative hours.

Can you help me?

[vincent_ardiet_]

Maybe this one is working:
SUM(AGGR( RangeMax(0,SUM(Hours)) ), [Name Employee]))

[Patrick_Pescetto]

Hello Vincent

Thanks for your answer.

The expression is not working. I use this:

SUM(AGGR( RangeMax(0, Sum({<[Tipo Datos Hechos]={'Turnos'},[Check Ausencia]={'0'}>}[Total Horas]) ), [Nombre Empleado]))
-
SUM(AGGR( RangeMax(0, Sum({<[Nombre Tienda]=>}[Total Horas Teoricas]) ), [Nombre Empleado]))

If you look at the screenshot, I have selected 2 employees and the result should be 3.50 hours, it should not take into account the -0.23 of the first line which is the first employee who has negative hours. That is why it shows 3.27 because it is taking all the employees results, without evaluating whether they are negative.

In the table, I can put a IF condition if the hours are negative, put NULL() value, but I can't get it for the line chartt.
It is possible for line chartt?

I hope you can help me. Thank you so much.

[vincent_ardiet_ · Accepted solution]

If I'm not wrong, what could be negative is not [Total Horas] neither [Total Horas Teoricas], this is the subtraction of both, right? You want to exclude an employee who has done less hours than he should have done in theory isn't?
In this case you should write the expression like this:
 
SUM(AGGR( RangeMax(0, Sum({<[Tipo Datos Hechos]={'Turnos'},[Check Ausencia]={'0'}>}[Total Horas]) - Sum({<[Nombre Tienda]=>}[Total Horas Teoricas]) ), [Nombre Empleado]))

[Patrick_Pescetto]

It's working, thank you so much!

And final question: If I want to do the same but only SUM the employees that have negative hours, how can I do it?

I see you put '0' in RangeMax() but I want to only sum the employees that have negative hours, not positive. Can you help me?

Thanks again.

[vincent_ardiet_]

If you still want to see the total of hours, you can just use RangeMin() instead of RangeMax() you will have only negative values.

[Patrick_Pescetto]

It's working. Thank you so much again for all of your help!!

Have a nice day!