Skip to main content
Adaptive Insights
Knowledge @ Adaptive Insights LLC, a Workday company.

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.

Before You Begin

How You Get There

Compass.png 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:

A subtotal calculation that uses a blank spacer to separate accounts

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 a difference of two elements in a tier such as the difference between two versions, two levels, or two customers. 

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. 

sample report shows the use of a Difference calculation element

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. (Optional) 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.
    • 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. 

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.

Use a custom calculation to subtotal elements that are not near each other

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.  

Use a custom calculation to create a metric or percentage

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 account with a negative number format.

sample report shows the use of a Custom calculation element

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.
  • Was this article helpful?