Can AGGR dimension be dynamic?

[markp201]

I have a pivot table with a linear guage (bar) for the measure.

I have three dimensions: region, manager, salesrep

MAX(AGGR(sum(sales),salesrep)) yields the max metric across all employees but I want the max for salesrep under the manager under the region.

Same for the other dimensions.  If the pivot only shows region, the max should be for the region, not the sales rep.

I've tried a few variations with dimensionality

MAX(AGGR(sum(sales),PICK(DIMENSIONALITY(),region,manager,salesrep)))

but this often resulted in Max Value appearing on all the rows.

If there is only one dimension its simple but what needs to be done for multiple dimensions ?

[sunny_talwar]

How do you decide your dimension? is it a cycle or drill-down group?

[markp201]

Neither - its a pivot table with 3 dimensions.  The pivot table does the drill-down.

[Anonymous]

I had the same problem but didn't find a solution...

For cyclic groups you can use getcurrentfield(), but  for dynamically expanded / collapsed pivots this is not possible I think.

[romansavchuk]

Did you try to move Pick with Dimentionality out of aggr function?

PICK( DIMENSIONALITY(), MAX(AGGR(sum(sales),region)), MAX(AGGR(sum(sales),manager)), MAX(AGGR(sum(sales),salesrep)) ).

I know that it`s not best solution from repeating code point of view, but maybe it`s solve your problem.

[petter]

I would think that this might be want you want to get calculated:

Pick(Dimensionality()

, Max(TOTAL Aggr(Sum(Amount),Region,Manager,SalesRep))

, Max(TOTAL <Region> Aggr(Sum(Amount),Region,Manager,SalesRep))

, Max(TOTAL <Region,Manager> Aggr(Sum(Amount),Region,Manager,SalesRep))

)

Or if you want the total sum per dimension and the max amount for the dimension :

Pick(Dimensionality()

, Max(TOTAL Aggr(Sum(Amount),Region))

, Max(TOTAL <Region> Aggr(Sum(Amount),Region,Manager))

, Max(TOTAL <Region,Manager> Aggr(Sum(Amount),Region,Manager,SalesRep))

)