Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be just this
Sum(Aggr(factor2, Region, manager))
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))
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!
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.
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))