Percentage of Total Revenue and Percentage of Total Expenses
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:
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:
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:
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.