BPC 10.1 on HANA : Percentage Average at Hierarchy Node level

[former_member419358]

Hello Everyone,

I have gone through couple of threads but couldn't find proper way to proceed. Please provide your input on the same.

1. I have to develop Finance report which has

        - Organization Dimension which has Hierarchy of All Units -> Unit 1, Unit 2 & Unit 3.

        - Account Dimension have values like Revenue, Tax, Expense and Tax % to Revenue.

        - Time Dimension have values like 2016.01, 2016.02,2016.03, 2016.Q1

I want to show MTD Finance report where Time dimension is in Context and based on the month I select from context, It should show MTD Finance report. You can see from above screenshot if user selects 2016.01 then it should show data for 2016.01 and likewise for 2016.02 and 2016.03 . which is easy and managed.

1. User wants to see the same Finance report at Unit level and consolidated also (Means All Units column). I have brought All Units node into BPC report but the only problem I have is Average of Percentage. It sums up all the values and percentage in BPC where as I want Percentage should get Average and values should get sum.

2. User also wants this report to be managed by Time Context. Means if he selects 2016.Q1 instead of 2016.01, 2016.02, 2016.03  then above report should look like below screenshot. Again here all the Accounts values like Revenue, Tax and Expense should be summed up but Tax% to Revenue should show Average of Percentage value for the entire quarter.

3. Last requirement I have is....I have Budget data for all Units. I want to show Budget, Variance and Variance % column for all Units individually and at consolidated level also.. I was able to achieve Variance by writing Member formula's (Actual-Budget) but I want to show Variance % value in Percentage which I am not able to get by writing member formula. Can anybody suggest how to achieve this ?

Did anybody come across this kind of scenario before ? Please provide your input on how this can be achieved ?.  Appreciate all your input help in advance.

Regards,

BI New

[former_member186338 · Accepted solution]

Tax % to Revenue - Dimension Member Formula.

Variance and Variance % - Local member in the report.

[Former Member · Accepted solution]

Hi Vadim,

Is this  helpful in this case? 

JP

[former_member186338 · Accepted solution]

Yes, but here everything is very simple...

Tax % to Revenue - is a Ratio KPI

Variances - something specific to current report.

[Former Member · Accepted solution]

Thanks Vadim!....

[former_member419358 · Accepted solution]

Hi Vadim,

Thanks for your reply.

1. Tax% to Revenue - It's a Ratio but it will have to show average if we execute report by selecting quarter and actual value when executed for specific month. Can you provide one example as what will you write in Member formula ?

2. Variances - Could you please provide details on Local Member formula ? I am not getting clear on the Formula syntax to be written. Current excel shows you 3 units but in actual I have more than 100 units. So, can you provide me one example syntax ?

Thanks again for your reply and help

[former_member186338 · Accepted solution]

Sorry, but please, start doing something yourself!

1. Read help about dimension member formulas and create a simple formula in the account dimension.

2. Read help about local members - examples!

By the way, report with 100 units x 4 columns per unit = 400 columns is completely unreadable

[Former Member · Accepted solution]

Hi,

For the local member, please create the local member Variance by attaching it to the member "Budget" of category and local member Variance % to local member Variance. In the formula you can put the formula as A1-B1 the cell references OR EPMMEMBER.[ACTUAL] - EPMMEMBER[BUDGET](dont exactly remember the syntax please check).

For Variance % put the formula within the local member or you can use the format as Percentage!

Hope this helps...

Regards,

JP

[former_member186338 · Accepted solution]

I see no reason to retype the sample from help: "16.4.3 Local Members - Examples"

[Former Member · Accepted solution]

Ohh....Is it? Believe me I dint copy it from there.....typed as I got it in my mind...Strange coincidence..

JP

[former_member186338 · Accepted solution]

I mean that topic author has to read help:

There is a clear sample named: "Calculate the difference between the forecast and the actual sales"

[Former Member · Accepted solution]

Yes Vadim......here is the link for him to read for more understanding...

https://help.sap.com/businessobject/product_guides/boeo10/en/EPMofc_10_user_en.pdf

Regards,

JP

[former_member186338 · Accepted solution]

What is not in help:

How to make the local member formula independent of CATEGORY selection:

=INDIRECT(ADDRESS(ROW(),COLUMN()-1))-INDIRECT(ADDRESS(ROW(),COLUMN()-2))

Or simple solution:

If the first data cell is B3 (ACTUAL) and BUDGET is C3 then

= C3-B3

But "Use Excel Cell References" has to be checked!

[former_member419358 · Accepted solution]

Hi Vadim / JP,

Thanks for your reply. I had created Local Member formula by creating samples and it worked but it got created for only one unit since I am having one more dimension called Measure below Version / Category..

Do I have to create same formula for all Units individually ?

[former_member186338 · Accepted solution]

Sorry, but " I am having one more dimension called Measure below Version / Category" - not clear!

I don't see it on the report...

P.S. It's a good idea to explain ALL the requirements from the beginning... not to waste time!

[Former Member · Accepted solution]

NO!.....Just try it in the system.....attach the member to category member Actual as per your screenshot. It will automatically be repeated for all the units!

Have you read the document??

Regards,

JP

[former_member186338 · Accepted solution]

He is doing something strange with MEASURES...

[former_member419358 · Accepted solution]

Yes. When I insert a column to add any local formula say A1-B1, then it's not created for all units.

Please find below two screen shots

1. Finance Report without Variance Column

2. Finance report after adding one column. It's not getting added for each unit.

[former_member186338 · Accepted solution]

Sorry, if you don't want to explain what you have for MEASURES then I am unable to help...

What do you mean by "Amount"?

[Former Member · Accepted solution]

Do not use Activate Local member recognition for this case. Directly go to Edit report and add a local member to the Local members tab! Attach it to Actual dim member.

Please explain what for Measures dim you are using?

Regards,

JP

[former_member419358 · Accepted solution]

Hi Vadim,

There was a Dimension Measure used in the model which had multiple values like Amount, Quantities, Gross value etc...so whenever any template was created for Budget or Forecast, We were using Measure dimension to segregate between Amount and Quantities. If they want to do budget for Quantities then from Measure dimension we select Quantities and if they want to budget for Amounts like Revenues, Expense etc.. then we were using Amount from Measure dimension.

Hope that answers your question.

[former_member186338 · Accepted solution]

OK, it's not a standard MEASURES dimension!

It's a custom dimension like KEYFIGURE or something like...

Please remove this dimension from column axis and place it on the page axis!

[former_member419358 · Accepted solution]

Hi Vadim,

Yes, It's a user defined key figure dimension.  If I take this dimension from Column axis then entire report will be blank since Amount field under MEASURE dimension was used while budgeting data also when we loaded data from BI to BPC, we have used this dimension and loaded actual values in Amount.

[former_member186338 · Accepted solution]

Please read my message once again - place it in the page axis with amount member!

[former_member419358 · Accepted solution]

Hi JP,

Thanks for your reply. I tried as you have mentioned, but it didn't work. I have explained above the use of Measure dimension.

[former_member186338 · Accepted solution]

Do you understand the meaning of "Page axis"?

The MEASURE dimension member (Amount) is the same for all columns - has to be on Page Axis!

Then your last dimension will be CATEGORY and you can attach the local member VARIANCE "after" CATEGORY dimension!

[Former Member · Accepted solution]

You must be doing something wrong......Show the screenshot!

Have you read the help documentation?

Regards,

JP

[former_member419358 · Accepted solution]

Thanks Vadim,

I was able to do it. Appreciate your help and input.

[former_member419358 · Accepted solution]

Hi JP,

I have read the document. I got it.

Thanks for your help and input.

[former_member186338 · Accepted solution]

Hi, JP

The issue is the useless last dimension MEASURE. He has to remove it before insertion of local member.

[Former Member · Accepted solution]

Yes....seems he got it finally.... . But is it really the correct answer? .

JP

[former_member186338 · Accepted solution]

I am testing my answers in the system

[Former Member · Accepted solution]

NO.....I dint mean that! I know you do it.....What I meant was for others users searching the forum....the answers should be clear and to the point. As per me this answer should have been marked as correct because it answers all the queries stated in post! Sorry if I hurt your feelings...

Sorry, but please, start doing something yourself!
1. Read help about dimension member formulas and create a simple formula in the account dimension.
2. Read help about local members - examples!

By the way, report with 100 units x 4 columns per unit = 400 columns is completely unreadable 

JP

[former_member419358 · Accepted solution]

Hi JP,

Appreciate your warm response. The issue was useless but new to me since I am new to report designing. I just had to remove Measure dimension from Column axis to Page axis but I missed it and Vadim mentioned in his reply.

For the query Tax % to Revenue which is a ratio and as I had mentioned in my first page.... if we run it for any particular month then it should show the exact percentage but if we execute for any quarter or year then it should show Average which needs to be resolved by Member formula as per Vadim which I will work on tomorrow and update.

Also 100 units divided across 20 users. So, each user will be able to see 5-7 units at the most but CXO of the company requires MTD report for all the Units and Consolidated together in one excel.

Thanks again.