% Variance caliculation in BPC 10.1 Report

[former_member253127]

Hello Experts,

I am calculating percentage of variance with respect to Excel reference and I activated local member recognition for local member creation.  After formula designed in Excel result values are going to be  zero (0) automatically. If I calculates the same using normal excel formula with out local member reference its working fine. Kindly help me for the same with local member.

Below is the formula I am using.  I noticed as A1 and B1 are custom formatted columns.   

=IF(B1=0,0, (A1-B1)/B1) 

Regards Raji P

[former_member186338]

Instead of local member recognition please create local member formula yourself based on help!

Please provide a screenshot of your report and explain what do you have in the columns...

Vadim

[former_member253127]

Hi Vadim

Thanks for your suggestion, I am facing same problem with direct  local member creation and below is the screen shots for the same.

Regards

Raji P

[former_member186338]

Looks like you are also having issues with screenshots...

[former_member253127]

Sorry for the inconvenience I have attached the screen shots again.

[former_member186338]

And what is the formula generated by local member?

And what column letters you have?

Please describe everything, I can't check you local member without clear picture!

Vadim

[former_member253127]

Hi Vadim,

I am using =IF(EPMPOSITION(2)=0,0,(EPMPOSITION(1)-EPMPOSITION(2))/EPMPOSITION(2))

This report having total four columns . selected year actual (A), previous year actual (B), current year budget(C) and Variance (A-B) .Variance is calculated by using local member  and now I am calculating % Variance using above formula with local member, but result is coming as zero .

Regards

Raji P

[former_member186338]

Sorry, bu no answers:

"And what is the formula generated by local member?

And what column letters you have?"

I am talking about formula on the Excel sheet generated after local member was inserted and resulting in zero

P.S. I can't see your Excel file...

[former_member253127]

HI Vadim,

Excel sheet  generated formula is =IF(Q37=0,0,(P37-Q37)/Q37

Local member formula is =IF(EPMPOSITION(11)=0,0,(EPMPOSITION(10)-EPMPOSITION(11))/EPMPOSITION(11))

Report having total four columns those are C_Actual, P_Actual, C_Budget and variance.

Now I want to insert fifth column and it should calculate % of variance .

Excel reflecting formula and local member formula is fine but calculation result is not fine.

P37= C_Actual (-160310278.29)

Q37=P_Actual (-163519311.29)

R37=C_Budget

S37=Variance (P37-Q37) (3209033)

T37= % Variance (0)

Above mentioned are actual values of report .

Regards

Raji P

[former_member186338]

I have checked - it's not zero!

It's:  -0,01962

You have to format this column as Percent and you will have: -1,96% (with 2 decimals)

[former_member253127]

HI Vadim,,

I have tried the same before initiating conversation, It's not taking to the result value.

And I noticed those column1 and column values are custom formatted , is causing the any problem..?

[former_member186338]

Sorry, but I don't see the percent sign on the screenshot in the last column. Custom format of other columns has no effect!

[former_member253127]

Yes Vadim, Percentage sign is not there because is not getting applied by using format cells option. if I use the same formula with out using local member that time format cells are applying fine.

[former_member186338]

Format column manually to test. Then configure correct format using formatting sheet!

Vadim

[former_member253127]

HI vadim,

I tested separately the format and I am trying to apply the same using view formats , But its not applying.

I am using Formatting on specific member\ proper for adding local member and applying format

Data :100000.00%

Use : Number format

Regards

Raji P

[former_member186338]

Sorry, but you have to show detailed formatting sheet screenshot!

There are a lot of things that can be done incorrectly!

Vadim