Calculation of a ratio
Dear Community,
I would like to supplement the presentation of a profit and loss statement with a ratio.
This should always show the percentage of the upper node. This means, for example, the percentage share of sales in total output or the share of personnel costs in total costs.
As the ratio is to be calculated and displayed dynamically down to the account level, I need a dynamic calculation.
Can anyone help me with this?
Thank you very much!
9 replies
-
Hi Jona, I think what you are looking for is a context calculation. It is covered in the following section of the help. https://help.pyramidanalytics.com/Content/Root/MainClient/apps/Discover/PRO/Calculations/contextCalcs/Context%20Calculations.htm?Highlight=context%20calculations
Right click on column heading of the column to calculate the percentage ratio. Then select "query options' -> 'quick calc' -> 'create measure' -> 'cumulative measure' -> 'percentage of total'. This will create a new column.
In the drop zones right click on the new column and change the calculation logic to 'by Parent' to get the child items to show the percentage of the parent.
-
Jona, on a second review I might have misunderstood your question. The explanation is in English but the P&L table is German. Before I take another guess would you be able to more clearly state the ratio calculation you would like to achieve. With reference to the columns, rows and amounts. Thanks!
-
, I think your guess was not that bad - but's table seems to need a more precise specification of the requirements, too. As I know German - and Jona from a training we had together - I visualized what he most probably wants to achieve:
Example: In line 1 ("Umsatz" = "Sales") 56.864 is 99,24% of 57.296 ("Gesamtleistung" = "Total Output") and so on. The second block deals with Costs and most probably should act in the same way - but the rest of the table is not clear to me, as "EBITDA", "Abschreibungen", "Finanzergebnis", "Neutrales Ergebnis" und "Steuern" do not have such a parent-child-relationship as the lines above.
Hope this helps!
Michael -
I'm sorry if I formulated my question too imprecisely.
has described exactly what I want to achieve with the calculation. So thank you very much!
We can leave out the lines "Abschreibungen", "Finanzergebnis", "Neutrales Ergebnis" und "Steuern" in the analysis. For “EBITDA”, it would be great if the field were simply left blank. However, I don't know whether this is technically feasible.
If you need any more information, please let me know.Thank you very much!
Jona
-
-
its quick and easy if you know how.............
It sounds like you simply want to divide a measure value for the current account by the value of its parent account.
Graphical Method
Start a new Formula in Formulate. Select the data model.
- Add a data point element block and select the measure (maybe "PL" above)
- Go to and select the accounts hierarchy (see below in my example). Click the small Fx button on the top right.
- Now click on "current member"
- Add a divide operator to your formula, and add another data point block as above. Reselect the same measure.
- Select the account hierarchy again. This time, instead of current member, choose parent.
And its done!
Script Method
Instead of all the clicking, change to script mode and simply build out the following formula with your specifics:
([Measures].[Accounting Amount],[Account].[Account].CurrentMember)/ ([Measures].[Accounting Amount],[Account].[Account].Parent) -
thank you very much. The calculation now works.
Is it also possible to exclude certain elements from the hierarchy from the calculation?
For example, the key figure makes no sense for “Gesamtleistung” (Total operating revenue), “Gesamtkosten” (Total costs), “EBITDA”, “Abschreibung” (Depreciation and amortization), “Finanzergebnis” (Financial result), “Neutrales Ergebnis” (Non-operating result), “Steuern” (Taxes) and “Ergebnis vor Ergebnisverwendung" (Profit before appropriation of profit).These elements should therefore be hidden. However, it could become complicated as the elements come from different levels of the hierarchy.
Many thanks in advance!
-
use IF statements to check if the current account is a specific member. Notice the OR statement "||" and the "NULL" value.
if( ([DimAccount].^[DimAccount Hierarchy].CurrentMember IS [DimAccount].^[DimAccount Hierarchy].[47].[48]) || ([DimAccount].^[DimAccount Hierarchy].CurrentMember IS [DimAccount].^[DimAccount Hierarchy].[47]), NULL, ([measures].[FactFinance Amount],[DimAccount].^[DimAccount Hierarchy].CurrentMember)/ ([measures].[FactFinance Amount],[DimAccount].^[DimAccount Hierarchy].CurrentMember.Parent) ) -
Nice. Thank you so much!
One last question. I promise
I still have the problem that the percentage value aggregates over the months when I use a cumulative view.
Here is the example for March as a single month and then for the months January to March.
Do you understand what I mean?Thank you!
-
you can try changing the solve order, so percentage logic runs AFTER the aggregation.
-