add total row (Calculated)

[pgalvezt]

Hi,

Searching into the forum I found an app from jagan who explain how to add a calculated row. I tried to do the same thing with my app but the total appears in the column: I would like sum top 3 elements. I dont want include ADMINISTRACIÓN. I attached the Jagan Sample and my app. Can you explain me how to do this... because total in my app appears in column.

Departamento	Ene	Feb
1.INGRESOS DE LA EXPLOTACIÓN	750.962.418	400.996.625
2.COSTO DE VENTAS	-503.155.054	-267.907.385
3.OTROS INGRESOS DE LA EXPLOTACIÓN	5.936.236	446.977
4.REBATE PROVEEDORES	0	-7.000.000
Total	253.743.600	126.536.217
ADMINISTRACIÓN	-51.136.540	-31.918.623

Thanks!!

[pgalvezt]

Jonathan, almost total look very well in the position but the total appears = 0 I attached the final app with your changes.

[JonnyPoole · Accepted solution]

add the new pivottabledimension to the aggr as well:

Sum(Aggr(

If(cc_group_desc='1.INGRESOS DE LA EXPLOTACIÓN' or cc_group_desc='3.OTROS INGRESOS DE LA EXPLOTACIÓN'

or cc_group_desc='4.REBATES',Sum(ppto_clp_Real_01),Sum(ppto_clp_Real_01))*-1,cuenta,PivotTableDimension))

//Sum(ppto_clp_Real_01)

[pgalvezt]

If I left just Sum(ppto_clp_Real_01). Total is good, but the other values have changed. If I change the dimmension by yours total = 0. Can you edit my app for better comprehenssion. I think I missing something.

Thanks.

[JonnyPoole]

ok here is everything . my previous post i suggested 'solution2' below. In your example, i tweaked it a little to create solution1. 

I think either will work for this sample. do some more testing and see which one you are comfortable with.

Solution 1:

Sum(Aggr(

If(PivotTableDimension='1.INGRESOS DE LA EXPLOTACIÓN' or PivotTableDimension='3.OTROS INGRESOS DE LA EXPLOTACIÓN'

or PivotTableDimension='4.REBATES' or PivotTableDimension='Total' ,Sum(ppto_clp_Real_01),Sum(ppto_clp_Real_01))*-1,cuenta,PivotTableDimension))

Solution2 :

Sum(Aggr(

If(cc_group_desc='1.INGRESOS DE LA EXPLOTACIÓN' or cc_group_desc='3.OTROS INGRESOS DE LA EXPLOTACIÓN'

or cc_group_desc='4.REBATES'  ,Sum(ppto_clp_Real_01),Sum(ppto_clp_Real_01))*-1,cuenta,PivotTableDimension))

[pgalvezt]

Jonathan your solution before was the correct. I apologize my mistake because 2014 wasn't selected that is the reason

1.INGRESOS DE LA EXPLOTACIÓN

750.962.418

400.996.625

If I select Año 2014 appears the correct result.

One more thing Jonathan in my app I change the text format in the tab expression drill down sum sign

Teext Format:

=If(Grupo='MARGEN DE EXPLOTACIÓN' or Grupo='TOTAL COSTO DE ADMINISTRACION Y VENTAS' Or Grupo='TOTAL RESULTADO DE LA EXPLOTACIÓN' or Grupo='TOTAL INGRESOS FUERA DE LA EXPLOTACION'

Or Grupo='TOTAL EGRESOS FUERA DE LA EXPLOTACIÓN' or Grupo='TOTAL RESULTADO FUERA DE LA EXPLOTACIÓN'

Or Grupo='RESULTADO ANTES DE COSTO DE ADMINISTRACION' Or Grupo='TOTAL RESULTADO ANTES DE IMPUESTO' Or Grupo='RESULTADO EJERCICIO','<B>',Grupo)

So, total was in bold text. How can I left total like that (Bold Text) Everything the total and the numbers of the totals.

Thanks for your help.

[JonnyPoole]

i don't know if i understood 100%. but to BOLD the 'Total' row i did this:

[pgalvezt]

Total appears now with bold but the total numbers not.

[JonnyPoole]

Pablo, go to the expressions tab and expand the expression the same way you did in the dimensions tab.

Put the same expression in the 'text format' as above .

You need to do it for each expression.

[pgalvezt]

Many thanks Jonathan.