Can XAVI formulas compare data across departments, locations, or subsidiaries?
Yes. Every XAVI formula that pulls period-based data accepts optional filter arguments, subsidiary, department, location, and class. By placing those values in the rows or columns above your formulas and referencing those cells, you can build a single report that compares any dimension side by side.
This article walks through a department comparison as a concrete example. The same pattern works for location, subsidiary, class, or any combination — department just happens to be one of the most common use cases.
How does the layout work?
The key idea is simple: put shared parameters (year, subsidiary, period, etc.) somewhere fixed so every formula references them. Then use the column or row headers to vary the one dimension you want to compare.
In the example below, months run across the columns and departments run down the rows. Every formula is structurally identical — the only thing that changes per column is the period, and per row is the department name.
Example: Monthly Sales by Department
The shared parameters — year, subsidiary, location, and class — are set once at the top and don't change. The department in each row and the month in each column are what drive the comparison.
Shared: Year 2025 | Subsidiary: Acme Corp | Location: (all) | Class: (all) | Shared: Year 2025 | Subsidiary: Acme Corp | Location: (all) | Class: (all) | Shared: Year 2025 | Subsidiary: Acme Corp | Location: (all) | Class: (all) | Shared: Year 2025 | Subsidiary: Acme Corp | Location: (all) | Class: (all) | Shared: Year 2025 | Subsidiary: Acme Corp | Location: (all) | Class: (all) | Shared: Year 2025 | Subsidiary: Acme Corp | Location: (all) | Class: (all) | Shared: Year 2025 | Subsidiary: Acme Corp | Location: (all) | Class: (all) |
Department | January | February | March | April | May | June |
East | 413,200 | 389,700 | 441,100 | 398,600 | 462,300 | 478,900 |
West | 298,400 | 311,500 | 287,900 | 305,200 | 319,700 | 334,200 |
Central | 187,600 | 201,200 | 214,500 | 198,800 | 223,100 | 238,600 |
South | 142,300 | 128,900 | 156,700 | 163,200 | 171,400 | 184,800 |
All Departments | 1,041,500 | 1,031,300 | 1,100,200 | 1,065,800 | 1,176,500 | 1,236,500 |
The formula in every data cell follows the same pattern — only the period and department cell references change:
=XAVI.TYPEBALANCE("Income", C$4, C$4, C$2, C$5, etc.)
Because the department and period are cell references rather than hardcoded values, you can change any row label or column header and the entire grid re-queries NetSuite automatically.
Does this work for other dimensions too?
Yes — department is just one example. The same column-reference pattern works for:
Location — compare offices, warehouses, or regions
Subsidiary — compare legal entities side by side
Class — compare product lines, channels, or cost pools
Combinations — fix one dimension globally and vary two others across rows and columns simultaneously
Is this a complete solution or a starting point?
It's a starting point. The layout shown here is meant to give you a working foundation quickly — a live, formula-driven grid you can build on. From here you might add variance columns, budget comparisons, additional account types, or conditional formatting. XAVI formulas are fully customizable, so once the pattern is in place, extending it is straightforward.
What if I want to add a department or a month?
New department: Insert a row, type the department name, and copy a formula from an adjacent row. The department reference updates automatically.
New month: Insert a column, update the period in the column header cell, and copy a formula from an adjacent column.
No structural changes to the rest of the report are needed.