sum of rows in pivot when using column()

[karolina_]

Hi All

I know help says "Pivot table totals are always calculated as expression total."
But maybe someone findout workaround for this so I don't need to change expression:

Column(3)-Column(4)-if(Column(2),(Column(5)-Column(6))*Column(1),0

to huge sums with aggr?

Each of column is set analysis with variables.

Simple example to play with attached.

[Anil_Babu_Samineni]

Hello Karolina,

What are you expecting and where we need to work.

[karolina_]

I would like to have total for last column with expression Column(3)-Column(4)-if(Column(2),(Column(5)-Column(6))*Column(1),0  to be SUM OF ROWS not total of expression
I want to keep Column() function, not rewriting it to huge expression

[prma7799]

Please share expected output...

Here i am not understanding about >>> " Column(3)-Column(4)-if(Column(2),(Column(5)-Column(6))*Column(1),0  "

[karolina_]

[prma7799]

Convert this pivot chart  into straight chart >> Expression > Total Mode >set as Sum

[karolina_]

Question is about pivot. I know straight table can do this - I even post a screen of it to show needed value.

[Frank_Hartmann · Accepted solution]

try this expression:

=sum(aggr(sum(aggr(sum(F3),F5,F6))-sum(aggr(sum(F4),F5,F6))-if(sum(aggr(sum(F2),F5,F6)),(sum(aggr(sum(F1),F5,F6))

-sum(aggr(sum(F2),F5,F6))

-sum(aggr(sum(F2),F5,F6))/(sum(aggr(sum(F1),F5,F6))

-sum(aggr(sum(F2),F5,F6))))*sum(aggr(sum(F1),F5,F6))

,0),F5,F6))

it will not work using column():

Column total in pivot table not summing properly

[karolina_]

In my org app I use sums with set analysis and variables so such expression would be quite big.

I also display part of this expression in different columns for example Column(3)-Column(4) is column(7).

If sum of rows would be somehow possible in pivot I would use simple Column(14)-Column(11)

if not I will need write expression with all sums and aggrs

[Frank_Hartmann]

i guess you will have to write the expressions with sum and aggr.

As Sunny mentioned in this post, column function is not working in this case!

hope this helps