cancel
Showing results for 
Search instead for 
Did you mean: 

BPC grand total and Sub total % of grand total question

Former Member
0 Kudos

Hi Gurus

I am trying to develop an EVDRE report.Let me try to explain the problem.

I am trying to develop the sales report of customer and page break and sub total at on sales zone.At the end grand total of all the sub total . SO far life was good.Now as per requirement after each sub total ( just below the sub total ) I have to insert % ( percentage sales of sub total to grand total ). The problem I am facing is circular reference to % sub total.If I try to to copy from another cell then it break when I change the member set. Let me explain by example.The sub total and sub total % are in afterRange. My problem is calculation and display of sub total % just below the sub total.Any suggestion is welcome and appreciated.

NA AL 2

FL 4

GA 6

Sub total YY

% sub total x% ( yyX 100/yy+xx)

LA BR 1

MX 3

CO 5

Sub total XX

Sub Total % y% ( xx* 100/yy+xx)

Grand Total YY + XX

Accepted Solutions (1)

Accepted Solutions (1)

former_member186498
Active Contributor
0 Kudos

Hi Rajesh,

if you expand dynamically you must write 2 evdre() with different RowKeyRange f.e. you want the Account in E column from the 22 row and the value in the G column, the first RowKeyRange should be E22:E23 and the second E26:E27, in G24 and G28 you put the subtotal "=SUM(G22:G23) and =SUM(G26:G27)" and in G25 and G29 you put the percentage "=G24100/(G24+G28)" and "=G28100/(G24+G28)", in G30 you put the total "=SUM(G24:G28)", so it works without circular reference.

Best regards

Roberto

Former Member
0 Kudos

Hi Robert

Actually the member set is not just two otherwise I would have used hard coded excel formula or VB.The problem is number if member set is unknown. So in example I have given NA and LA but actually it is more then that and very large number. But it is clear that you have understood my problem very well. Just in place of 2 members of account dimension ,think it more then 11.

Hope u can give some lead.

Kind regards

Rajesh

JohnL
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Rajesh,

You can achieve this requirement using an AfterRange (After Range) within a SortRange (Sort Range).

Take a look at at this [example in ApShell|http://i39.tinypic.com/2rdd81u.jpg]. You will need a property defined in your dimension that identifies the grouping. In my example the property is REGION. I added ENTITY.REGION in the first column parameter of the Sort Range. I then added an After Range in cell D33:E34 taking advantage of the EVSUB and %KEY% features.

The only way I can think of to get the sub total percentages is to use an EVGET for the denominator which looks up the TOTAL member of your entity dimension (I am assuming NA, LA, etc all belong to your Entity dimension).

Screenshot: [http://i39.tinypic.com/2rdd81u.jpg]

Thanks,

John

Former Member
0 Kudos

Thanks John .It was perfect.

Former Member
0 Kudos

Hi John,

I am trying to insert a row after each account rollup (to do a percentage) in our standard detailed Income statement. So the report has base and parent level accounts going down the rows (Account Dimension memberset is set to "all,parentafter,self") and then actual, budget and variances going towards the columns. I did a simple afterrange (not in the sort range) and did a formula where it says if the account is a calculated member give me the percentage. The problem is, its only inserting the row and putting the percentage on the last row only (in this case, after Net_Income) , but I want to get a row with percentage after each rollup (revenue, operating expense, etc). I tried different memberset options with no luck. Do know what I am doing wrong? Or if there is another way to do this.

Thanks.

JohnL
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Sabrina,

I understand what you're trying to do ( a percentage of total I presume). I can't think of a dynamic way of doing this using evDRE. You may need to hard-code the report.

Have you thought about using dimension formula's or script logic if you want to make this dynamic?

The only issue with that is you may need to create new members which signify a rollup %.

I haven't tried this in the new EPM Add-In either but I suppose using a local member formula might do the trick.

Let me know how you end up implementing this and I'll let you know if I think of anything else.

Thanks,

John

Former Member
0 Kudos

John,

Thank you so much for the quick reply. You are right, a percentage of total is exactly what I am trying to achieve. I haven't thought about using dimension formulas or script logic. But now that you mentioned it, I can see how it can be a possibility. I think I will have to create an alternate account hierarchy which will be the mirror of current account hierarchy with new members for percentage added. Since this report will be used by only one business unit, I don't want to make changes to our primary hierarchy. Most people will still use the original view of the Income statement.

I will let you know what I end up doing.  

Thanks.

Answers (1)

Answers (1)

former_member186498
Active Contributor
0 Kudos

Hi,

you use an excel formula to calculate the perc? Can you write the formula you used?

Best regards

Roberto