# How do i caculate a percentage on subtotals?

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

• # How do i caculate a percentage on subtotals?

In palo excel i want to create a column with percentages. The percentages should be calculated dynamically/automatic.

I know i can calculate a percentage with a rule like
['Percentage'] = ['Fte'] / ['Grand total', 'Fte']

However, in stead of 'Grand total' i need to calculate on subtotals/each c-element: ['Percentage element 1'] = ['Fte'] / ['Subtotal elements 1-4', 'Fte']
['Percentage element 2'] = ['Fte'] / ['Subtotal elements 1-4', 'Fte']
['Percentage element 3'] = ['Fte'] / ['Subtotal elements 1-4', 'Fte']
['Percentage element 4'] = ['Fte'] / ['Subtotal elements 1-4', 'Fte']
['Percentage element 5'] = ['Fte'] / ['Subtotal elements 5-6', 'Fte']
['Percentage element 6'] = ['Fte'] / ['Subtotal elements 5-6', 'Fte']
..
etc.

Because i have a lot of subtotals/c-elements making a separate rule for each subtotal/c-element is not an option.

Can this be done with 1 or a few rules?
• Hi marc,

I haven't tried this yet and I'm not sure if I understood everything correctly, but my approach would be something like this:

['Percentage'] = ['FTE'] / PALO.DATA("DB", "Cube", PALO.EPARENT("DB", "Dimension", !'Dimension', 1), 'FTE');

This would of course only work if you only have one parent for those elements. Otherwise you would have to add something to consider more than one parent as well.

Hope that helps or gives at least some ideas.

Christian
Christian Herzog, Senior Consultant, Vector SW DV GmbH
• Hello Christian,

"This would of course only work if you only have one parent for those elements. Otherwise you would have to add something to consider more than one parent as well.": this is exactly my problem: I do have mutiple parents and i do not want to manually insert each parent in the palo.data function.

I think the problem can be solved by adding a new measure ('parenttotal') to my cube. In Excel i will then be able to build two columns: one with the value of the individual elements and one with the corresponding parenttotal. The percentage can then be calculated with a rule: value column individual element divided by value column parenttotal.

So my question is: how to extract the values of consolidated elements into the measure/column 'parenttotal'?

gr.
Marc
• Attached is an example of what i am trying to do (see my previous post).
• Hi marc,

here is how it works for your example:

['parenttotal'] = PALO.DATA("Demo","Sales",IF(PALO.EINDENT("Demo","Products",!'Products') == 1,!'Products',PALO.EPARENT("Demo","Products",!'Products',1)),!'Regions',!'Months',!'Years',!'Datatypes',"Units")

['percentage'] = ['Units'] / ['parenttotal']

Still, the challenge is what to do in case you have more than one parent to a child. I would try to solve that in the report rather than in the database.

Hope this helps
Holger
• Hi,

why not use EISCHILD instead of EINDENT?

Regards.
Robert Tischler
Senior Consultant Vector SW DV GmbH

The post was edited 1 time, last by tish1 ().

• Hello Holger,

The past two days i tried your solution and..... it's working great! Thank you very, very much for your help.

gr.
Marc
• Tish,

I'm still learning on the rules. Personally i think the Palo manual should be more elaborate on the topic 'rules'.

Can you give me an example of a rule with palo.eischild and palo.eisparent?

gr.
Marc
• Hi,

PALO.EISCHILD returns true, if one element is a child of a certain parent.

### Source Code

1. PALO.EISCHILD("localhost/Demo";"Regions";"West";"Germany")

Regards.
Robert Tischler
Senior Consultant Vector SW DV GmbH