Calculating Avg using Aggr() in pivot table

[Anilb1603]

I have sales data at Region, Country and BU level.

I want to create a pivot table with 3 dimensions Region, Country and BU in indent rows mode like a drill down and wants to calculate Avg(Cost) by country when I was in Region or Country level but when I drill down to BU level I want to use just Sum(Cost).

Region+             

               Country+    

                                 BU  

For the first two rows I want to use  Avg(Aggr(Sum(Cost), Country)) and for the third row I want to use just sum(Cost).

Can someone please help me on this??

[Anilb1603]

Did anyone get a chance to look at this?? Any help would be appreciated.

[sunny_talwar]

May be this

If(Dimensionality() = 3, 
   Sum(Cost),
   Avg(Aggr(
      Sum(Cost)
   , Country))
)

[Anilb1603]

Thanks Sunny,

It was working fine when I have BU as a third dimension but its not gonna be 3rd dim always. May user wants to drag it to 1st position as he can change the dim order in pivot table the way he wants. I think I need to put a condition that checks the dimension name.

Is there any other way to solve this???

[sunny_talwar]

You can use GetObjectField() function to determine what field is there... for Example when Region is your first dimension... GetObjectField(0) should give you Region