Distribution Ratio by using local member

[Former 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

[former_member186338 · Accepted solution]

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

[Former Member · Accepted solution]

Hi Vadim,

PERFECT! Our issue is solved immediately by the way you informed!

Thank you for your kind support!

Best regards,

Masa

[former_member186338 · Accepted solution]

For sure in this simple formula example there are limitations like: only simple hierarchy (2 levels) of ITEM is supported, all base members need to have parent!

But this approach can be adjusted to other requirements!

Vadim

[Former Member · Accepted solution]

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