Dynamic Total <> in a pivot table

[ali_hijazi]

Hello I got a pivot table in which I want to calculate the investment sum and a ratio
suppose my expression for Investment is sum(measure)
and the expression of the ratio is sum(measure) / sum(total<Quarter, Investment Type,...> measure)

I have a problem in the calculation of the Ratio
when the dimensionality is 2 (Investment type) then the denominator in the expression of the Ratio should be
sum(measure) / sum(total<quarter> measure)
when the dimensionality is 3 like at the level of ISBM or NFR the ratio should be:
sum(measure / sum(total <quarter, InvestmentType>measure)
and  when the dimensionality is 4 like where there is MX3 ISBM1.... the expression should be
sum(measure / sum(total <quarter, InvetmentType, InvestmentName> measure)
and so on...

kindly advise on how to accomplish this

[marcus_sommer]

You may query the dimensionality and then branching into the wanted expression, maybe like:

sum(measure) / pick(match(dimensionality(), 0,1,2),
   sum(total<quarter> measure),
   sum(total<quarter, X> measure),
   sum(total<quarter, X, Y> measure))

[ali_hijazi]

I tried this but the thing is that the order of dimension can be changed by the user
i also tried the GetObjectDimension function
it works without actually specifying the actual name of the dimension
However now I want to generate $(=GetObjectDimension($1)) dpending on the dimensionality
something I'm unable to accomplish
using the GetObjectDimension will make it easier when user changes the order of dimension,
but I need something to generate $(=GetObjectDimension(0)), $(=GetObjectDimension(1)) when Dimensionality() = 2 and so on

sum

(

    total<$(=GetObjectDimension(0)),$(=GetObjectDimension(1))>

        {

        <

            SOURCE={MANDAYS_TIMES_DW}

                ,[Sub-Category]-={"*"}-{"is train","mx train"}

                ,[Consultant Division ID]={PRDCTDIV}

                ,DISPLAY_RECORD={1}

                ,[Epic/CBR Type]-={"Other","Program**Production Staging"}

            >

        }

        MAN_DAYS

    )

[marcus_sommer · Accepted solution]

The following didn't return the used dimensions in regard to the dimensionality() ?

pick(match(dimensionality(), 0,1,2),
  GetObjectDimension(0),
  GetObjectDimension(0) & ', ' & GetObjectDimension(1),
  GetObjectDimension(0) & ', ' & GetObjectDimension(1)& ', ' & GetObjectDimension(2))

[ali_hijazi]

well yes I was almost there