Weird Customer Request, is this possible??

[#1]

Hi guys, is it possible to get a row minus a row if is a calculated value in a pivote table??? I mean there is a value already but I need to substract the value of other row, any idea??

its just a substraction but i can´t figure it out jet.

In other words, what I need is the that the value in Row "CUOTA NOMINA" minus value Row  "NOMINA_HRAS" , the value resultant will replace the value in Row "CUOTA NOMINA".

I know is kind of weird but its a customer request.

I hope I explained myself.

Thank you for your time!!

I attached the app and a power point to explain better myself.

[swuehl]

I think you get what you ask for using:

=sum( aggr(IF(ACCOUNT_NAME = 'NOMINA_HRAS', sum({$<ACCOUNT_NAME = {'MINA_HRAS'}>}[Costo por hora]* [Hrs Reales]),

SUM(Gastos)),ACCOUNT_NAME,COCE_NAME))

+

sum({$<ACCOUNT_NAME = {"NOMINA_HRAS"}>}[Costo por hora]* [Hrs Reales])

-

if(ACCOUNT_NAME = 'CUOTA NÓMINA',sum(total {$<ACCOUNT_NAME = {"NOMINA_HRAS"}>}[Costo por hora]* [Hrs Reales]),0)

Not sure if there is not a better way to do all this, but since I don't understand what you are calculating here, I just can't say.

Hope this helps,

Stefan

[#3]

Yes Stefan, that´s exactly what I needed thank you, but there is just one think missing, the Total is not the correct value, is not doing the Sum.

[swuehl · Accepted solution]

Right, you need to use an additional advanced aggregation:

=sum( aggr(IF(ACCOUNT_NAME = 'NOMINA_HRAS', sum({$<ACCOUNT_NAME = {'MINA_HRAS'}>}[Costo por hora]* [Hrs Reales]),

SUM(Gastos)),ACCOUNT_NAME,COCE_NAME))

+

sum({$<ACCOUNT_NAME = {"NOMINA_HRAS"}>}[Costo por hora]* [Hrs Reales])

-

sum(aggr(

if(ACCOUNT_NAME = 'CUOTA NÓMINA',sum(total {$<ACCOUNT_NAME = {"NOMINA_HRAS"}>}[Costo por hora]* [Hrs Reales]),0)

,ACCOUNT_NAME,COCE_NAME))

[#5]

Wow!! yes Stefan it works perfectly, Thank you very much, you are the man!!