Skip to Content
avatar image
Former Member

Distribution Ratio by using local member

Hi experts,

I want to create the report contains distribution ratio (%) as follows:

Precise:

-Members of the combination of Parent and Children in ITEM dimension are always placed in the report.

-Of course the number of items and item groups are gradually increasing year by year.

Then I want to define "ratio" as a local member.

However, I don't know how to point the cell of aggregate value in the definition of local member such as the row number of "Item Group nn".

What I had tried is:

-use EPMPOSITION : not possible

-use OFFSET function (xls) : not possible

-etc

In this case, isn't it possible to realize the report by using local member... Please teach me how to define it.

Best regards,

Masa

WS000001.JPG (54.9 kB)
Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Best Answer
    Sep 17, 2014 at 09:32 PM

    Hi Masa,

    To my mind, your requirements can't be solved by local member formula. But it's possible to create dimension member formula for ratio (new ACCOUNT member) using MDX syntax.

    Something like (just description):

    If current member of dimension ITEM is a base member ([CALC]='N') then divide [Qty] by parent ITEM,

    If not - then 1

    Like:

    IIF([ITEM].CURRENTMEMBER.PROPERTIES("CALC")="N",[Qty]/([Qty],[ITEM].CURRENTMEMBER.PARENT),1)

    Vadim

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Vadim Kalinin

      Hi Vadim,

      Yes, i see! I wrote the formula to be propotion of each parent except top of hierarchy. It is our client's requirement.

      Have a good weekend!

      Best regards,

      Masa