XAVI.TYPEBALANCE returns the total balance for all accounts that belong to a specified account type or special account type.
Use this formula when you want to summarize financial data by category rather than specifying individual account numbers.
For example, instead of listing every Income account individually, you can use XAVI.TYPEBALANCE to return the total for all Income accounts.
The CFO Flash Report available from Quick Start is a good example of how XAVI.TYPEBALANCE can be used to build a summarized financial report.
XAVI.TYPEBALANCE Syntax
The formula follows this structure:
=XAVI.TYPEBALANCE(accountType, fromPeriod, toPeriod, [subsidiary], [department], [location], [class], [accountingBook], [customSegment])
The first parameter determines the account type included in the calculation.
Income
=XAVI.TYPEBALANCE("Income", fromPeriod, toPeriod, [subsidiary], [department], [location], [class], [accountingBook], [customSegment])
Returns the balance for accounts categorized as Income.
Cost of Goods Sold
=XAVI.TYPEBALANCE("COGS", fromPeriod, toPeriod, [subsidiary], [department], [location], [class], [accountingBook], [customSegment])
Returns the balance for accounts categorized as Cost of Goods Sold.
Operating Expense
=XAVI.TYPEBALANCE("Expense", fromPeriod, toPeriod, [subsidiary], [department], [location], [class], [accountingBook], [customSegment])
Returns the balance for accounts categorized as Expense.
Other Income
=XAVI.TYPEBALANCE("OthIncome", fromPeriod, toPeriod, [subsidiary], [department], [location], [class], [accountingBook], [customSegment])
Returns the balance for accounts categorized as Other Income.
Other Expense
=XAVI.TYPEBALANCE("OthExpense", fromPeriod, toPeriod, [subsidiary], [department], [location], [class], [accountingBook], [customSegment])
Returns the balance for accounts categorized as Other Expense.
See XAVI.TYPEBALANCE in the CFO Flash Report
You can generate the CFO Flash Report to see how XAVI.TYPEBALANCE works in a complete report.
Open ExtendInsights Financial Reporting.
Go to Quick Start.
Select Profit & Loss Report.
Choose the Fiscal Year.
Under CFO Flash Report, click Generate.
After the report is generated, select cell C6.
Review the formula in the Excel formula bar.
For example:
=XAVI.TYPEBALANCE("Income",C$5,C$5,$Q$3,$R$3,$S$3,$T$3,$U$3,,$W$3)
In the CFO Flash Report, this formula returns the total revenue for the period in cell C5, using the filters referenced by the formula.
Rather than specifying individual revenue accounts, XAVI.TYPEBALANCE calculates the balance based on the Income account type.
Using Filters in the CFO Flash Report
The CFO Flash Report also demonstrates how you can make XAVI formulas dynamic by referencing filter cells.
In the generated report:
P2 contains the filter headers.
P3 contains the values used to filter the report.
Depending on your NetSuite configuration, these filters can include dimensions such as:
Subsidiary
Department
Location
Class
Accounting Book
Custom Segments
When you change a supported filter value, the XAVI formulas use that value when calculating the report.
This approach lets you build one report structure and change the reporting scope without manually rewriting every formula.
Drill Down into a TYPEBALANCE Result
You can use Drill Down to review the underlying data that makes up a XAVI.TYPEBALANCE result.
Using the CFO Flash Report as an example:
Select cell L6, which contains the
XAVI.TYPEBALANCEformula.In the ExtendInsights task pane, select Drill Down.
Review the transactions that contribute to the calculated amount.
Drill Down is useful when you need to understand how XAVI arrived at a balance or investigate the activity behind a financial category.
When to Use XAVI.TYPEBALANCE
Use XAVI.TYPEBALANCE when you want to:
Summarize balances by account type instead of individual account numbers.
Create high-level financial reports such as a CFO Flash Report.
Build formulas that automatically include accounts belonging to a specified account type.
Analyze a financial category while applying Subsidiary, Department, Location, Class, Accounting Book, or Custom Segment filters.
For reports that require specific accounts or account ranges rather than an entire account type, use the appropriate XAVI balance formula instead.