EPM Calculate %

[Former Member]

Hello Experts,

I have new in the BPC 10.0 and i have a doubt repect to the % calculation.

i have a report with this structure.

                     periodic         %        

A                    100           100%

      B                50             50%

      C                50             50%

D                    200            100%

       E               50              25%

       F             100              50%

       G               50             25%

I have a hirarchy wiht level, and i need calculate the porcentaje of the amount repect to the total of the parent level.

in the example A is the level 0 and its value is 100 with represent the 100%

                        B is a child of A and its value 50 represent the 50% of his parent A.

and so on.

how can i do this calculation in BPC 10.0?

Regards.

Carlos.

[former_member186338 · Accepted solution]

Ups, sorry...

Your case is slightly different! You have to create custom measure...

[Former Member · Accepted solution]

Dear Vadim,

I agree with you in that i need to create a Custom measure.

I try to create this but like i have new in BPC, i have many problems and i don´t now how to create the logic to calculate the % on base the level of the hirarchy.

I have create a Custom Mesaure in the transaction se38 in the report UJA_MAINTAIN_MEASURE_FORMULA.

And I can create a custom measure like this

'IIF([%ANALITICA%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC", 0,1)'; SOLVE_ORDER = 3

But my problem is that in sentence IF i need to ask for the parent in the hierarchy and then sum the measure of this parent for divide for the measure of the currentmember.

i attach a screenshot and my idea if filtre the custom measure but the field parenth1.

Could you help me?

regards.

Carlos

[former_member186338 · Accepted solution]

You have to understand MDX language: https://technet.microsoft.com/en-us/library/aa216781(v=sql.80).aspx

[Former Member · Accepted solution]

Dear Vadim,

I have create the next custom measures in the se38 with the report  UJA_MAINTAIN_MEASURE_FORMULA and the functionlity of this custom measures is that if the property ACCTYPE of the ANALITICA is "EXP" then the result is 100%, else we want to SUM all values of the ANALITICA with his parent in the hierarchy are "OTROS ING", "OBRA_CURSO", "PREST_SERV" or "ING_FIN".

'IIF([%ANALITICA%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",(([MEASURES].[/CPMB/SDATA]/[MEASURES].[/CPMB/SDATA])*100),SUM([%ANALITICA%].LEVELS("ING_FIN"), [%ANALITICA%].LEVELS("PREST_SERV"), [%ANALITICA%].LEVELS("OTROS_ING"), [%ANALITICA%].LEVELS("OBRA_CURSO"), [%ANALITICA%].CURRENTMEMBER,-[MEASURES].[/CPMB/SDATA]))';SOLVE_ORDER=3

But the query give the next error  MDX statement error:"Invalid MDX command with ," Message no. UJO_READ_EXCEPTION000

And i don´t know what is the error.

Regards.

CArlos.

[former_member186338 · Accepted solution]

What do you mean by [%ANALITICA%].LEVELS("ING_FIN")??? It's not about parents...

You have to read more about MDX...

[former_member186338 · Accepted solution]

P.S. And experiment with simple formulas

[Former Member · Accepted solution]

Dear Vadim,

My custom Measures now is working,

'IIF([%ANALITICA%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",(([MEASURES].[/CPMB/SDATA]/([%ANALITICA%].[PARENTH1].[ING]))),(([MEASURES].[/CPMB/SDATA]/([%ANALITICA%].[PARENTH1].[COSTE_DIR]))))';SOLVE_ORDER=3

I only have one doubt, if i want to check what is the parent of node, what sentence i have to use, because if i use

'IIF([%ANALITICA%].[PARENTH1]="INC" OR 'IIF([%ANALITICA%].CURRENTMEMBER.[PARENTH1]="INC"

Doesn´t work.

Could you help me?

Regards.

Carlos

[former_member186338 · Accepted solution]

First - I do not understand what you are trying to achieve...

Second - why do you think that this syntax will work?

[%ANALITICA%].[PARENTH1]="INC"

INC is the property value...

[former_member186338]

Percent is a KPI or Ratio figure. And you have to use dimension member formula to calculate KPI's. Please read:

Vadim