Formula rows let you build your own calculations in a report by adding, subtracting, multiplying, and dividing any data in ScaleXP.
A formula row created here is local to the report you are editing. For a reusable calculation you can use across reports, dashboards, and presentations, build it as a metric instead — see How to edit a metric and change the way a metric is calculated (how to change a formula). Both use the same formula builder, so the steps below apply in either place.
How to create a new formula row
You can add a formula row when editing any report. Go to Reports (top bar), open a report, then select Edit (top right) > Edit rows.
At the bottom of the rows, click Click to Start .
You can add a row with a single data point from your accounting system, CRM system, Google or Excel sheet, or a ScaleXP metric or customer page report.
In this case, choose Formula to create a nested calculation of your choice.
How to select the data source
Click a row to choose its data source.
The options are:
- Accounting — accounts and groups of accounts from your accounting system;
- CRM - data from HubSpot, Salesforce, or Pipedrive pipelines and stages
- Google Sheets or Microsoft Excel — data and KPIs connected from your spreadsheets;
- ScaleXP — your metrics, customer and CRM data, and manual-entry data;
- Fixed Number
- Row
- Brackets
Use the Type to search box to find a specific item, and the ( ) button to add brackets to a calculation.
Once the data is added, you can further edit with tracking codes/classes/locations and time periods.
How to add tracking codes or classes or locations to a formula
To report a line by Xero tracking codes or QuickBooks classes or locations, click the row, open its settings (the cog), and use the Classes / Category selector. Tick the codes or classes to include, then Save.
How to change to cash basis reporting (Xero only)
If you are using Xero as your accounting input, you can also select the cash rather than accrual version of a line item. Note: you can't select classes or categories and cashflow at the same time. Be sure to Save changes.
How to set the time period for a row
Each line can use its own time period, always relative to the report's reporting month. Click the row, open its settings (the cog), and under Time Period choose the type and the period (for example, Value for 1 month ago):
- Value — a single month, for example the current month;
- Add — the total across a range of months, for example the past 3 months;
- Average — the average across a range of months;
- Change — the difference in value between two dates, for example the increase in active customer numbers.
How to combine rows into a formula
Once you have added data, make further edits such as:
- change the mathematical function (sign at left).
-
change the data source — click on the row to edit it further:
- change the data source (first dropdown);
- change the data source detail, such as a specific report on the Customers tab (second dropdown, if available);
- select which entities to include (consolidated entities only);
- add brackets;
- add a fixed number;
Add a row for each piece of data, then set the calculation type for each using the symbol at its left: Add, Subtract, Multiply, or Divide.
Use brackets to control the order of operations: click the ( ) button, then drag rows into the bracketed group using the six dots at the left, or add new data inside the brackets where it says Click here to add a new row.
.png)
How to format the result
Use the controls on the formula total row to set how the result is named, formatted, and aggregated.
Rename using the pencil icon
Rename the metric or report row here.
Select output format (monetary, numerical or percent)
Use the dropdown menu at the far right of the formula total row to select the output number type: monetary, numerical, or percentage.
Other formula properties (cog icon)
Decimals — select the number of decimals here. In reports, this overrides the overall format for the whole page and is applied only to the row.
Variance — determines whether an increase in the metric value is treated as good, bad, or neutral.
YTD aggregation — choose between four options to calculate multi-month time periods such as year to date, quarter to date, full year, and quarter.
-
Default summation — uses rules based on the mix of components in the formula:
- for example, if any of the components are balance sheet or "last," it returns the value from the most recent month; if all values in the formula are from the P&L, it sums the component parts and applies the formula;
- common uses: % margin, basic P&L or balance sheet reports.
-
Ending value — always returns the value of the last month in the series:
- for example, YTD January to March returns the value for March;
- common use: number of customers, ending cash balance, other balance sheet reports.
-
Starting value — always returns the value of the first month in the series:
- for example, YTD January to March returns the value for January;
- common uses: opening cash balance, beginning customer numbers.
-
Cumulative monthly — returns the sum of the monthly outputs in the series:
- for example, YTD January to March returns January plus February plus March;
- common uses: cash flow reports, total new customers.
When done, be sure to Save.
How to delete a row
Click the row, then select the trash icon.
How to move a row
Use the six dots at the left of the row to drag it into position.
