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.
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:
| Name | Value.name | Value.orgName | Value.type | Value.reportingCode | Value.account | Value.dateMonth | Value.trackingOption | Value.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.
