Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
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
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.
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))
Wow!! yes Stefan it works perfectly, Thank you very much, you are the man!!