# Add Calculations to a Matrix Report

Explains how to add a calculation in a matrix report to display a subtotal, a variance, or a custom formula.

Add the following calculations to your matrix report:

• Subtotal: Calculate a mathematical subtotal of the preceding rows or columns. Use blank spacers to isolate specific accounts to subtotal.
• Difference: Calculate the variance between two elements in a tier.
• Custom: Create a formula that refers to the elements that are in a tier.

Use the following guidelines when adding a calculation to a report. See Formula Syntax for Custom Calculation Elements in Reports for more.

• Reference only the elements that are of the same data type. For example, a calculation on accounts only references account elements.
• Create a tier before you drag a custom calculation element into the tier. For example, to calculate on versions, first create a versions tier. Then drag a custom calculation element into the tier.
• Use a calculation on any area of a report including rows, columns, sheet tab, or filter.
• Reference other dynamic elements that are in the report. These elements must be of the same data type as the calculation.
• Reference other absolute elements that are not in the report. These elements must be of the same data type as the calculation.
• Reference a report parameter through an element that connects to the parameter. See Report Parameters for more.
• Reference other calculations of the same data type. For example, a calculation for time can reference another variance calculation on time. The calculation cannot reference another variance calculation on versions, levels, or products.

See List of Formula Examples for sample formulas you can use with your calculations.

## How You Get There

From the nav menu, select Reports. Open a matrix report and select Modify Report. In the left pane, expand the Calculations element.

## Add a  Subtotal

Use a subtotal element to display a mathematical sum of the preceding elements. The value does not consider the time, level, or dimension roll-ups that you have set for the accounts under Model Management.

For example, say your matrix report includes the following GL accounts on the rows:

• Expenses and Allocations
• Cost of Goods Sold
• Net Income
• Non-Operating Income

You want to subtotal the Net Income and Non-Operating Income accounts only. Your saved report looks like this:

Do the following:

1. Drag the Subtotal calculation element after the Non-Operating Expenses account.
2. (Optional) Drag a Blank spacer between Cost of Goods Sold and Net Income.
3. Save and run the report.

## Add a Variance

Use the Difference element to calculate the variance of two elements in a tier such as the variance between two versions, two levels, or two customers.

### Display the Variance Between Two Versions

For example, say your matrix report includes the following versions on the columns:

• Actuals
• Last Year Budget - Approved

The report includes the following GL accounts on the rows:

• Net Income
• Expenses and Allocations
• Assets
• Liabilities and Equities

You want to calculate the variance between the two versions and track this value over time.

Do the following:

1. In the report columns, drag the Difference calculation element after the versions.
2. Right-click the Difference calculation element and select Properties. Edit the following properties:
• Change the label to "Variance".
• Select the Reverse Sign checkbox to change the sign (from minus to plus) for specific GL accounts.
• Select Difference Options. Next, select the Subtract Version and the From Version.
There are many other properties that you can edit for a calculation such as style, conditional formatting, and number formatting.
• Select Apply.
3. Save and run the report.

### Display the Variance Between Two Versions and Two Time Periods

For example, say your matrix report includes the following versions on the columns:

• Working Budget for 2019
• Forecast for 2018 through 2020

The report includes the following GL accounts on the rows:

• Revenue
• Non-Operating Expenses
• Cost of Goods Sold
• Operating Expenses

You want to calculate the variance between the two versions that crosses two time periods, 2019 and 2020.

Do the following:

1. In the report columns, drag the Difference calculation element after the versions.
2. Right-click the Difference calculation element and select Properties. Edit the following properties:
• Change the label to "Variance Between Forecast and Working Budget".
• Select Difference Options. Note that the Subtract Version is Working Budget and the From Version is Forecast.
• To display FY 2019 for the Forecast version, select Year for Stratum, 1 for Offset, and Forward.
There are many other properties that you can edit for a calculation such as style, conditional formatting, and number formatting.
• Select Apply.
3. Save and run the report.

## Add a Variance for Elements Not Shown in the Report

You can add a variance for elements that are not shown in the report. For example, say a report displays the FY 2018 and FY 2019 time elements. You can add a preceding Difference element to calculate the variance between FY 2016 and FY 2017. FY 2016 and FY 2017 are not shown in the report.

The monthly finance variance report for executives is another example. The report shows the following information:

• Current period actuals
• Delta to prior period actuals
• Actual delta to budget

The prior period and budget are not show in the report, but they are included in the variance calculation.

## Add a Custom Calculation

Use a custom calculation to:

• Sub-total other sub-totals or elements that are not next to each other. The Subtotal element only sums the preceding rows or columns.
• Create a metric or a percentage.
• Display an expense as a negative value. By default, both income and expense accounts display as positive values.
• Reverse the sign of an account that is not an expense, liability, or equity account.

See Use the Formula Assistant for details on how to create a formula.

### Subtotal Elements Not Near Each Other

For example, say your report includes GL accounts on the rows. You want to subtotal the Net Income and Non-Operating Income accounts to see your total income.

Do the following:

1. In the report rows, drag the Custom calculation element below the last GL account.
2. Right-click the Custom calculation element and select Formula Assistant.
3. Search for the Net Income and Non-Operating Income accounts and insert into the formula.
4. In the Formula field, enter the following formula:
`RPT.3900_Net_Income+RPT.5100_Non_Operating_Income`
5. Select OK to close the Formula Assistant.
6. Right-click the Custom calculation element and select Properties. Enter the label "Total Income".
7. Save and run the report.

### Create a Metric or a Percentage

For example, say your report shows revenue, cost of goods sold, and gross margin (using a Difference element). Use a custom calculation to show gross margin percentage as a percent of revenue.

Do the following:

1. In the report rows, drag a Custom calculation element below Gross Margin.
2. Right-click the Custom calculation element and select Formula Assistant.
3. In the Formula field, enter the following formula:
`div (RPT.Difference, RPT.4000_Revenue)*100`
4. Select OK to close the Formula Assistant.
5. Right-click the Custom calculation element and select Properties. Enter the label "Gross Margin Percentage".
6. Save and run the report.

### Display an Expense as a Negative Value

For example, say your report includes GL accounts on the rows. You want to display the Operating Expenses parent account with a negative number format.

Do the following:

1. In the report rows, right-click the 6000 Operating Expenses account and select Delete.
2. Drag a Custom calculation element to the placement for 6000 Operating Expenses.
3. Right-click the Custom calculation element and select Formula Assistant.
4. Either expand GL Accounts or search for 6000 Operating Expenses.
5. Select the 6000 Operating Expenses account and insert into the formula.
6. In the Formula field, after the account syntax, enter "`*-1`". Select OK to close the Formula Assistant.
7. Right-click the Custom calculation element and select Properties. Edit the following properties:
• Enter the label "6000 Operating Expenses".
• Select Numbers and then select a negative number format. For example, select a format that displays negative amounts with a minus (-) sign. Select Apply. The row formatting overrides any report formatting.
8. Save and run the report.

### Reverse the Sign for an Account

The Reverse Sign option on a Difference element only reverses the sign of the difference for Expense, Liabilities, Current Liabilities, Long Term Liabilities, and Equity account types in the General Ledger structure. The option does not apply if an account is not one of these account types.

You can use a custom calculation to reverse the sign of an account that is not an Expense, Liability, or Equity account. In the calculation you can put a negative sign in front of the account to reverse the sign.

For example, say your report displays the following accounts on the rows:

• Gross Margin
• Liabilities
• Equity

You add a Difference element to show the change between the Liabilities and Equity accounts for FY 2019 and for the Working Budget version. You select the Reverse Sign property on the Difference element to reverse the sign (from plus to minus) for the difference amount. Since Gross Margin is not an expense, liability, or equity account, you use a custom calculation to flip the sign for this account from plus to minus.

Do the following:

1. In the report rows, drag a custom calculation below the Gross Margin account.
2. Right-click the custom calculation element and select Formula Assistant.
3. In left pane, search for or browse to the Gross Margin account and select Insert into Formula.
4. In the Formula field, add `-1*` before the account code for Gross Margin. For example: `-1*[PL_GM].`
5. Right-click the custom calculation element and select Properties. Change the label to Gross Margin
6. Select Ok. Save and run the report.