Handling Group Companies with US and Non-US Xero Organisations: Sorting out the Negatives

July 23, 2024

Understanding Xero Reporting and Display Values:

It is important to note that the Xero API for Profit & Loss as well as Balance Sheet reports provide you will integers as a ‘Display Value’ for a report or financial statement. This is different to the Trial Balance report that provides the integers in debits and credits. Where a debit is a positive and a credit is a negative.

This can cause issues when providing consolidated reports across multiple Xero organisations in different regions of the world as the Display Values behave differently in certain Xero Business Editions.

Regional Differences in Display Values:

Non-US Xero Organisations (AU, NZ, UK, Global): Account types like Overheads, Expenses, and Direct Costs will appear as positive values if the account has a debit balance and negative for credit balances.

US Xero Organisations: These regions behave differently. The same account types (Overheads, Expenses, and Direct Costs) will be displayed as negative values for debits and positive for credits.

Resolving the Issue:

Your data table in Excel or Power BI will have the following headers:

NameValue.nameValue.orgNameValue.typeValue.reportingCodeValue.accountValue.dateMonthValue.trackingOptionValue.trackingOption2

To achieve consistency when working with data from both US and Non-US Xero organisations, we can utilize Excel or Power BI. Here's how:

In an additional column to the right, please add an IF statement to the Value. Type column, like the below:

=IF(OR([@[Value.type]]="DIRECTCOST",[@[Value.type]]="OVERHEADS",[@[Value.type]]="EXPENSE"),-[@[Value.amount]],[@[Value.amount]])

This will switch the integers for the relevant account codes.

IMPORTANT - you must only apply this IF statement to the US organisations to get them to match the Non-US Display Values. To truly automate this in a data set with US and Non-US Xero Organisations, you will need to add an additional IF statement to only switch the integers in the correct instances.

learn more

Why is PowerBI important for Accountants?

As an accountant, you may find yourself referring to multiple sources to track the performance of your client’s cash flow, profitability, business trends and even bookkeeping cycle. What if you could access those results and comparisons automatically refreshed, on-demand, from your tablet, smartphone, PC, or even wearable device? Business intelligence and data visualisation systems can provide accountants with – the critical data and organisation summary in a custom format in one (or more) visuals or a dashboard. One of these powerful business intelligence tools is PowerBI.
READ MORE

Power BI: What is DAX?

DAX (Data Analysis Expressions) is a formula expression language developed by Microsoft to interact with data in different analysis services and visualisation tools platforms like Power BI, PowerPivot and SSAS tabular models. DAX can seem familiar to competent excel users as the language uses similar formulas and expressions.
READ MORE

How to connect your Xero account(s) to AccountingBi

This guide will walk you through two methods for connecting your Xero accounting software to AccountingBi
READ MORE

How to call your profit and loss account reports from Xero to AccountingBi

We have outlined a step-by-step process of calling your profit and loss report data from your Xero account to AccountingBi - article and video.
READ MORE

How to call the balance sheet reports from Xero to AccountingBi

We have outlined the steps of calling balance sheet reports from Xero to AccountingBi in a video (watch below) and an article.
READ MORE

How to connect Microsoft excel to Xero through AccountingBi

This video and article provide steps for connecting Xero to PowerBi through AccountingBi. It assumes that you've already pulled the Xero API from accounting BI for your various reports.
READ MORE
1 2 3 8
READ MORE
Accounting.bi - Business Intelligence Software
HOW IT WORKS...
Copyright © 2024 Accounting.bi is a brand owned and operated by Accounting SQL Limited, incorporated in the United Kingdom. ​
Terms & Conditions - GDPR