Different calculated measures for drilldown hierarchy levels

[alandilworth]

Hello,  I am trying to determine how to have different calculations according to the drill down level.  In the pivot table below the top hierarchical row is for the Entity, and the drilldown row is for the Territory. 

I have the following formula to calculate the best month of sales for the Owner:

Aggr(if( Max(Total <GaltOwner> Aggr(Sum(MonthlySales), GaltOwner, Month)) = Sum(MonthlySales), Month) , GaltOwner , Month)

I want this formula to change to the following whenever a row is clicked on/drill downed:

Aggr(if( Max(Total <GaltTerritory> Aggr(Sum(MonthlySales), GaltTerritory, Month)) = Sum(MonthlySales), Month) , GaltTerritory, Month)

My problem is I do not know how to programmatically recognize if a row has been drilled down.

In pseudo code I’m wanting to accomplish this:

IF(Row is not drilled down, Aggr(if(Max(Total <GaltOwner> Aggr(Sum(MonthlySales), GaltOwner, Month)) = Sum(MonthlySales), Month) , GaltOwner , Month))

If(Row is drilled down, Aggr(if(Max(Total <GaltTerritory> Aggr(Sum(MonthlySales), GaltTerritory, Month)) = Sum(MonthlySales), Month) , GaltTerritory, Month))

Any suggestions will be greatly appreciated!

[mato32188 · Accepted solution]

Hi Alan,

try to use Dimensionality() function which returns the level of expanded row. Your expression might look like:

if(Dimensionality()=1, expression for low detail, 

if(Dimensionality()=2, expression for higher detail, etc.))

You can add Dimensionality() as a measure itself to check what is your "row level" in pivot table.

BR

m