Totals in a pivot-table missing, workaround with AGGR?

[Anonymous]

Hi,

Please consider the following pivot table:

Please note that the factor1 is the same for all dimensions, hence the total.

The problem is I cannot think of a good way to get the correct total under factor2

If i use the expression "Fact * MAX( TOTAL <Region,manager> factor2  ) I obviously get the wrong total because it uses the maximum factor2 on all lines.

I've been trying to get this to work with an AGGR function but I can't seem to pull it off, maybe I'm not thinking in the right way..

AGGR( factor2, Region, manager ) gives me the correct value per line but not a total (as you can see)

Anyone care to help me with this?

Kind regards,

Herbert

[sunny_talwar]

May be just this

Sum(Aggr(factor2, Region, manager))

[swuehl]

How does your "Fact" expression look like?

You would need to rewrite this expression in your aggr() if you want to multiply each line fact value with the factor, then sum it up, e.g. for an assumed Sum(FactField):

=Sum( Aggr( Sum(FactField) * factor2, Region, manager))

Use Aggregation Functions!

edit: and maybe you don't need the aggr() function, but this depends on your data model.

As always, context (here: details of your application) is key to a solution!

[Anonymous]

Thanks Sunny , but I'm not looking for the sum of the factors.

I'm looking for a way to calculate the "Fact * factor1 * factor2" per line and then summing that in the total.

[sunny_talwar]

Then may be try this

Sum(Aggr(Fact * factor1 * factor2, Region, manager))

Where Fact, factor1, and factor2 are field names and not expressions. If they are expressions, then replace the actual expression with the field name here

Sum(Aggr(FactExp * factor1Exp * factor2Exp, Region, manager))