By using this website, you agree to our Terms of Use (click here)
If you have a Profit & Loss and you want to add a column that shows each line as a percentage of the total, it gets a little tricky if you want to change the denominator of the calculation. For the revenue lines, you might want to show a percentage of Total Revenue. For the expense lines, you might want to show the percentage of Total Expenses. So the denominator changes as you move down the report.
Let's say you have a report that looks like this:
And you want to add another column showing each line in the Revenue section as a percentage of Total Revenue, then each line in the Cost of Revenue section as a percentage of Total Cost of Revenue.
First, you need to go into the Row Set and populate the Base Row column for each line which is how you tell each line what the denominator will be in the percentage of calculation.
Next, you need to go into the Column Set and add a new calculated column with the following formula:
=C/Value(@BaseRowCode,'C')
Note that @BaseRowCode reads from the Base Row column in the Row Set.
Also note that P in the Format row is the shortcode for formatting the result as a percentage.
In the screenshot above, if the denominator wasn't changing throughout the report, you could hardcode the row number instead of using @BaseRowCode. Let's say you wanted every single line to show the percentage of Total Revenue, you could use this simpler formula:
=C/C0200
But since we want a changing denominator, we have to use the more complicated formula above which uses @BaseRowCode.
Using the following formula, we get the following results:
=C/Value(@BaseRowCode,'C')
For those familiar with an now discontinued product called FRx, this is a familiar feature. It was designed explicitly for the application you outline above. The trick is to ensure to define and/or change the base row BEFORE you need to use in.
Nice write up.
If interested, folks can find additional documentation and examples in the F350 training guild starting on page 35.
F350AnalyticalReports2017R2.pdf
Is it possible to do this for each column? When I tried it only calculates the first % ?
The formula above references Column C, but you could easily add an additional calculated column that references Column B.
When I tried that it only calculates in one of the columns. 🙁
Are you referring to why your Column O is empty? Try changing the Type from GL to Calc.
Hey Tim,
Thanks for the article, it's been really useful. However, I've been having issues when trying to show a total line as a percentage of another line. It just returns 100%.
For example I'd like to see my COGS as a percentage of my total income, so I'm totalling my COGS and using the Base row of my total income.
As you can (hopefully) see in the screenshot, my percentage is coming back at 100%.
What does your Column Set look like?
Hello,
Is it possible to add the percentages to just rows that show Totals? I have not been successful using IIF statements referencing @BASEROWCODE. That formula only seemed to work when I filled in @BASEROWCODE for rows that did not include totals.
Thank Tim.
I managed to add percentage for all columns. The only rows don't show the % is for Totals.
For instance, I need to show % for Gross Profit out of Total Income, but still showing nothing.
