Tutorials \ Edit the account totals on standard OdataLink templates
A common requirement when designing Profit and Loss reports is to control the totals included on the reports.
By default, OdataLink provides an AccountsTotals table. This table controls how totals are structured and calculated. This endpoint drives the Income, Cost of Sales, Gross Profit, Operating Expenses and Net Profit totals. While this table is suitable for most standard reporting, there are situations where you want to either use different names or even add additional totals (such as EBITDA, EBIT or GP%).
This article provides approaches that can be used. All concepts in this article are explained from the perspective of the standard templates on our site.
Also note that most of this article is written from the perspective of those that use Xero. For those that use MYOB, the endpoints, tables and fields have slightly different names. (e.g. MYOB uses the term AccountClassification instead of AccountType).
This article covers:
- Understanding the AccountsTotals table and how it’s applied
- Two approaches to creating an alternate AccountsTotals hierarchy
- How to create add additional calculations such as GP%
Understanding the AccountsTotals table and how it’s applied
The AccountsTotals table provides the structure and names of the different totals. It includes fields that indicate whether they should show on the Profit and Loss or the Balance Sheet, whether they represent a classification of accounts or are a total, and which accounts to include in the calculation.
The AccountsTypes column (for Xero) or Classifications column (for MYOB) indicate which accounts to include in the calculation. This field includes the list of classifications that apply, as well as the DrCrSign field indicating whether to add the value or substract it. As an example, Gross Profit / (Loss) would list Income with a +1 DrCrSign value, but CostOfSales with a -1.
From this source table, two power queries exist:
- One to create the AccountsTotals list.
- Another to create the AccountsTotalsClassifications (MYOB) or AccountsTotalsTypes (Xero) list.
Relationships allow navigating from the Accounts to the AccountsTotals.
AccountsTotals → AccountsTotalsClassifications/AccountsTotalsTypes → AccountsClassifications/AccountsTypes → Accounts
This represents only the structure, however. In addition, DAX measures in the report use this information to calculate the values. So while the table represents the structure and data, the measure represents the logic. They work hand-in-hand together and must be seen as a single concept.
Two approaches to creating an alternate AccountsTotals hierarchy
There are a number of approaches that can be used to create and replace this default AccountsTotals hierarchy. While this article provides two approaches, the end goal is the same.
- Replace the AccountsTotals power query to list the rows you want to see.
- Replace the AccountsTotalsTypes or AccountsTotalsClassifications power query to control which accounts are included in which rows.
- Replace the AccountsTypes or AccountClassifications power query to control the master list of classifications and types.
- (optionally) Recategories the Type or Classification of accounts.
The two approaches we recommend are:
- Create an alternate AccountTotals hierarchy using Table.FromRecords
- Create an alternate AccountTotals hierarchy using Excel tables
Both approaches work equally well in both Power BI and Excel.
The advantage of the Table.FromRecords approach is that it does not introduce any external dependencies. You can create and edit it using standard Power Query language. However, it can look more complex if unfamiliar with the M language.
let Source = Table.FromRecords( { ... [AccountTotal="INCOME", AccountTotalName="Income", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=true, Order=1, VarianceImpactSign=1], [AccountTotal="DIRECTCOSTS", AccountTotalName="Cost Of Sales", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=true, Order=2, VarianceImpactSign=-1], [AccountTotal="GROSSPROFIT", AccountTotalName="Gross Profit / (Loss)", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=false, Order=3, VarianceImpactSign=1], ... ) in Source
The advantages of an Excel table approach is that it is easier to capture, maintain and visualise the structure. It does mean that you need to place this Excel Workbook in a shared location such as Sharepoint.
As always, we recommend you take the approach that feels more comfortable for you.
Approach A: Create an alternate AccountTotals hierarchy using Table.FromRecords
Our preferred approach is use the Table.FromRecords function of Power Query to create the AccountsTotals, the AccountsTotalsTypes table and the AccountsTypes table. (or, for MYOB, the AccountsTotals, AccountTotalsClassifications and AccountsClassifications tables).
Step A1. Download the AccountsTotals Table.FromRecords power queries
To simplify using this approach, we provide the following download files for both MYOB and Xero. Each download file contains the complete power query needed to replace the original ones.
Download files for Xero
Xero AccountsTotals Power Query.txt
Xero AccountsTotalsTypes Power Query.txt
Xero AccountsTypes Power Query.txt
Download files for MYOB
MYOB AccountsTotals Power Query.txt
MYOB AccountsTotalsClassifications Power Query.txt
MYOB AccountsClassifications Power Query.txt
AccountTotals example power query for Xero
let Source = Table.FromRecords( { [AccountTotal="INCOME", AccountTotalName="Income", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=true, Order=1, VarianceImpactSign=1], [AccountTotal="DIRECTCOSTS", AccountTotalName="Cost Of Sales", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=true, Order=2, VarianceImpactSign=-1], [AccountTotal="GROSSPROFIT", AccountTotalName="Gross Profit / (Loss)", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=false, Order=3, VarianceImpactSign=1], [AccountTotal="OTHERINCOME", AccountTotalName="Other Income", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=true, Order=4, VarianceImpactSign=1], [AccountTotal="EXPENSES", AccountTotalName="Operating Expenses", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=true, Order=5, VarianceImpactSign=-1], [AccountTotal="NETPROFIT", AccountTotalName="Net Profit / (Loss)", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=false, Order=6, VarianceImpactSign=1], [AccountTotal="ASSETS", AccountTotalName="Assets", IsProfitLoss=false, IsBalanceSheet=true, IsAccountClassification=true, Order=7, VarianceImpactSign=1], [AccountTotal="LIABILITIES", AccountTotalName="Liabilities", IsProfitLoss=false, IsBalanceSheet=true, IsAccountClassification=true, Order=8, VarianceImpactSign=-1], [AccountTotal="NETASSETS", AccountTotalName="Net Assets", IsProfitLoss=false, IsBalanceSheet=true, IsAccountClassification=false, Order=9, VarianceImpactSign=1], [AccountTotal="EQUITY", AccountTotalName="Equity", IsProfitLoss=false, IsBalanceSheet=true, IsAccountClassification=true, Order=10, VarianceImpactSign=1] }, type table[AccountTotal=text, AccountTotalName=text, IsProfitLoss=logical, IsBalanceSheet=logical, IsAccountClassification=logical, Order=number, VarianceImpactSign=number] ) in Source
AccountTotalsTypes example power query for Xero
let Source = Table.FromRecords( { [AccountTotal="INCOME", AccountType="REVENUE", DrCrSign=1], [AccountTotal="INCOME", AccountType="SALES", DrCrSign=1], [AccountTotal="DIRECTCOSTS", AccountType="DIRECTCOSTS", DrCrSign=1], [AccountTotal="GROSSPROFIT", AccountType="REVENUE", DrCrSign=1], [AccountTotal="GROSSPROFIT", AccountType="SALES", DrCrSign=1], [AccountTotal="GROSSPROFIT", AccountType="DIRECTCOSTS", DrCrSign=-1], [AccountTotal="OTHERINCOME", AccountType="OTHERINCOME", DrCrSign=1], [AccountTotal="EXPENSES", AccountType="EXPENSE", DrCrSign=1], [AccountTotal="EXPENSES", AccountType="OVERHEADS", DrCrSign=1], [AccountTotal="EXPENSES", AccountType="DEPRECIATN", DrCrSign=1], [AccountTotal="EXPENSES", AccountType="WAGESEXPENSE", DrCrSign=1], [AccountTotal="EXPENSES", AccountType="SUPERANNUATIONEXPENSE", DrCrSign=1], [AccountTotal="NETPROFIT", AccountType="REVENUE", DrCrSign=1], [AccountTotal="NETPROFIT", AccountType="SALES", DrCrSign=1], [AccountTotal="NETPROFIT", AccountType="OTHERINCOME", DrCrSign=1], [AccountTotal="NETPROFIT", AccountType="DIRECTCOSTS", DrCrSign=-1], [AccountTotal="NETPROFIT", AccountType="EXPENSE", DrCrSign=-1], [AccountTotal="NETPROFIT", AccountType="OVERHEADS", DrCrSign=-1], [AccountTotal="NETPROFIT", AccountType="DEPRECIATN", DrCrSign=-1], [AccountTotal="NETPROFIT", AccountType="WAGESEXPENSE", DrCrSign=-1], [AccountTotal="NETPROFIT", AccountType="SUPERANNUATIONEXPENSE", DrCrSign=-1], [AccountTotal="ASSETS", AccountType="CURRENT", DrCrSign=1], [AccountTotal="ASSETS", AccountType="INVENTORY", DrCrSign=1], [AccountTotal="ASSETS", AccountType="PREPAYMENT", DrCrSign=1], [AccountTotal="ASSETS", AccountType="BANK", DrCrSign=1], [AccountTotal="ASSETS", AccountType="FIXED", DrCrSign=1], [AccountTotal="ASSETS", AccountType="NONCURRENT", DrCrSign=1], [AccountTotal="LIABILITIES", AccountType="CURRLIAB", DrCrSign=1], [AccountTotal="LIABILITIES", AccountType="LIABILITY", DrCrSign=1], [AccountTotal="LIABILITIES", AccountType="TERMLIAB", DrCrSign=1], [AccountTotal="LIABILITIES", AccountType="PAYGLIABILITY", DrCrSign=1], [AccountTotal="LIABILITIES", AccountType="SUPERANNUATIONLIABILITY", DrCrSign=1], [AccountTotal="NETASSETS", AccountType="CURRENT", DrCrSign=1], [AccountTotal="NETASSETS", AccountType="INVENTORY", DrCrSign=1], [AccountTotal="NETASSETS", AccountType="PREPAYMENT", DrCrSign=1], [AccountTotal="NETASSETS", AccountType="BANK", DrCrSign=1], [AccountTotal="NETASSETS", AccountType="FIXED", DrCrSign=1], [AccountTotal="NETASSETS", AccountType="NONCURRENT", DrCrSign=1], [AccountTotal="NETASSETS", AccountType="CURRLIAB", DrCrSign=-1], [AccountTotal="NETASSETS", AccountType="LIABILITY", DrCrSign=-1], [AccountTotal="NETASSETS", AccountType="TERMLIAB", DrCrSign=-1], [AccountTotal="NETASSETS", AccountType="PAYGLIABILITY", DrCrSign=-1], [AccountTotal="NETASSETS", AccountType="SUPERANNUATIONLIABILITY", DrCrSign=-1], [AccountTotal="EQUITY", AccountType="EQUITY", DrCrSign=11] }, type table[AccountTotal=text, AccountType=text, DrCrSign=number] ) in Source
AccountsTypes example power query for Xero
let Source = Table.FromRecords( { [AccountType="REVENUE", AccountTypeName="Revenue"], [AccountType="SALES", AccountTypeName="Sales"], [AccountType="DIRECTCOSTS", AccountTypeName="Direct Costs"], [AccountType="OTHERINCOME", AccountTypeName="Other Income"], [AccountType="EXPENSE", AccountTypeName="Expense"], [AccountType="OVERHEADS", AccountTypeName="Overhead"], [AccountType="DEPRECIATN", AccountTypeName="Depreciation"], [AccountType="WAGESEXPENSE", AccountTypeName="Wage Expense"], [AccountType="SUPERANNUATIONEXPENSE", AccountTypeName="Superannuation Expense"], [AccountType="CURRENT", AccountTypeName="Current Asset"], [AccountType="INVENTORY", AccountTypeName="Inventory"], [AccountType="PREPAYMENT", AccountTypeName="Prepayment"], [AccountType="BANK", AccountTypeName="Bank"], [AccountType="FIXED", AccountTypeName="Fixed"], [AccountType="NONCURRENT", AccountTypeName="Non-Current Asset"], [AccountType="CURRLIAB", AccountTypeName="Current Liability"], [AccountType="LIABILITY", AccountTypeName="Liability"], [AccountType="TERMLIAB", AccountTypeName="Long Term Liability"], [AccountType="PAYGLIABILITY", AccountTypeName="PAYG Liability"], [AccountType="SUPERANNUATIONLIABILITY", AccountTypeName="Superannuation Liability"], [AccountType="EQUITY", AccountTypeName="Equity"] }, type table[AccountType=text, AccountTypeName=text] ) in Source
Step A2. Modify the AccountsTotals Table.FromRecords power queries
To rename account totals, simply edit the corresponding AccountTotalName in the AccountsTotals Power Query.
...
[AccountTotal="INCOME", AccountTotalName="Revenue", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=true, Order=1, VarianceImpactSign=1],
...
To add new sections in your report, you need to:
- Add new rows to the AccountsTotals to provide their name and placement.
- Add new rows to the AccountsTotalsTypes table to provide logic driving which accounts to include.
- Add new rows to the AccountsTypes table to provide the master list of these new categories.
- Edit the Accounts table to recategory the accounts
The example below adds EBITDA and EBIT along with Depreciation, Amortisation, Interest and Tax.
let
Source = Table.FromRecords(
{
[AccountTotal="INCOME", AccountTotalName="Income", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=true, Order=1, VarianceImpactSign=1],
[AccountTotal="DIRECTCOSTS", AccountTotalName="Cost Of Sales", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=true, Order=2, VarianceImpactSign=-1],
[AccountTotal="GROSSPROFIT", AccountTotalName="Gross Profit / (Loss)", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=false, Order=3, VarianceImpactSign=1],
[AccountTotal="OTHERINCOME", AccountTotalName="Other Income", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=true, Order=4, VarianceImpactSign=1],
[AccountTotal="EXPENSES", AccountTotalName="Operating Expenses", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=true, Order=5, VarianceImpactSign=-1],
[AccountTotal="EBITDA", AccountTotalName="EBITDA", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=false, Order=5.1, VarianceImpactSign=1],
[AccountTotal="DEPRECIATION", AccountTotalName="Depreciation", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=true, Order=5.2, VarianceImpactSign=-1],
[AccountTotal="AMORTISATION", AccountTotalName="Amortisation", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=true, Order=5.3, VarianceImpactSign=-1],
[AccountTotal="EBIT", AccountTotalName="EBIT", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=false, Order=5.4, VarianceImpactSign=1],
[AccountTotal="INTEREST", AccountTotalName="Interest", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=true, Order=5.5, VarianceImpactSign=-1],
[AccountTotal="TAX", AccountTotalName="Tax", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=true, Order=5.6, VarianceImpactSign=-1],
[AccountTotal="NETPROFIT", AccountTotalName="Net Profit / (Loss)", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=false, Order=6, VarianceImpactSign=1],
[AccountTotal="ASSETS", AccountTotalName="Assets", IsProfitLoss=false, IsBalanceSheet=true, IsAccountClassification=true, Order=7, VarianceImpactSign=1],
[AccountTotal="LIABILITIES", AccountTotalName="Liabilities", IsProfitLoss=false, IsBalanceSheet=true, IsAccountClassification=true, Order=8, VarianceImpactSign=-1],
[AccountTotal="NETASSETS", AccountTotalName="Net Assets", IsProfitLoss=false, IsBalanceSheet=true, IsAccountClassification=false, Order=9, VarianceImpactSign=1],
[AccountTotal="EQUITY", AccountTotalName="Equity", IsProfitLoss=false, IsBalanceSheet=true, IsAccountClassification=true, Order=10, VarianceImpactSign=1]
},
type table[AccountTotal=text, AccountTotalName=text, IsProfitLoss=logical, IsBalanceSheet=logical, IsAccountClassification=logical, Order=number, VarianceImpactSign=number]
)
in
Source
Continuing from this, update the AccountsTotalsTypes table to drive these new totals.
let Source = Table.FromRecords( { [AccountTotal="INCOME", AccountType="REVENUE", DrCrSign=1], [AccountTotal="INCOME", AccountType="SALES", DrCrSign=1], [AccountTotal="DIRECTCOSTS", AccountType="DIRECTCOSTS", DrCrSign=1], [AccountTotal="GROSSPROFIT", AccountType="REVENUE", DrCrSign=1], [AccountTotal="GROSSPROFIT", AccountType="SALES", DrCrSign=1], [AccountTotal="GROSSPROFIT", AccountType="DIRECTCOSTS", DrCrSign=-1], [AccountTotal="OTHERINCOME", AccountType="OTHERINCOME", DrCrSign=1], [AccountTotal="EXPENSES", AccountType="EXPENSE", DrCrSign=1], [AccountTotal="EXPENSES", AccountType="OVERHEADS", DrCrSign=1], [AccountTotal="EXPENSES", AccountType="DEPRECIATN", DrCrSign=1], [AccountTotal="EXPENSES", AccountType="WAGESEXPENSE", DrCrSign=1], [AccountTotal="EXPENSES", AccountType="SUPERANNUATIONEXPENSE", DrCrSign=1], [AccountTotal="EBITDA", AccountType="REVENUE", DrCrSign=1], [AccountTotal="EBITDA", AccountType="SALES", DrCrSign=1], [AccountTotal="EBITDA", AccountType="OTHERINCOME", DrCrSign=1], [AccountTotal="EBITDA", AccountType="DIRECTCOSTS", DrCrSign=-1], [AccountTotal="EBITDA", AccountType="EXPENSE", DrCrSign=-1], [AccountTotal="EBITDA", AccountType="OVERHEADS", DrCrSign=-1], [AccountTotal="EBITDA", AccountType="WAGESEXPENSE", DrCrSign=-1], [AccountTotal="EBITDA", AccountType="SUPERANNUATIONEXPENSE", DrCrSign=-1], [AccountTotal="DEPRECIATION", AccountType="DEPRECIATN", DrCrSign=1], [AccountTotal="AMORTISATION", AccountType="AMORTISATION", DrCrSign=1], [AccountTotal="EBIT", AccountType="REVENUE", DrCrSign=1], [AccountTotal="EBIT", AccountType="SALES", DrCrSign=1], [AccountTotal="EBIT", AccountType="OTHERINCOME", DrCrSign=1], [AccountTotal="EBIT", AccountType="DIRECTCOSTS", DrCrSign=-1], [AccountTotal="EBIT", AccountType="EXPENSE", DrCrSign=-1], [AccountTotal="EBIT", AccountType="OVERHEADS", DrCrSign=-1], [AccountTotal="EBIT", AccountType="DEPRECIATN", DrCrSign=-1], [AccountTotal="EBIT", AccountType="AMORTISATION", DrCrSign=-1], [AccountTotal="EBIT", AccountType="WAGESEXPENSE", DrCrSign=-1], [AccountTotal="EBIT", AccountType="SUPERANNUATIONEXPENSE", DrCrSign=-1], [AccountTotal="INTEREST", AccountType="INTERESTINCOME", DrCrSign=-1], [AccountTotal="INTEREST", AccountType="INTERESTEXPENSE", DrCrSign=1], [AccountTotal="TAX", AccountType="TAX", DrCrSign=1], [AccountTotal="NETPROFIT", AccountType="REVENUE", DrCrSign=1], [AccountTotal="NETPROFIT", AccountType="SALES", DrCrSign=1], [AccountTotal="NETPROFIT", AccountType="OTHERINCOME", DrCrSign=1], [AccountTotal="NETPROFIT", AccountType="DIRECTCOSTS", DrCrSign=-1], [AccountTotal="NETPROFIT", AccountType="EXPENSE", DrCrSign=-1], [AccountTotal="NETPROFIT", AccountType="OVERHEADS", DrCrSign=-1], [AccountTotal="NETPROFIT", AccountType="WAGESEXPENSE", DrCrSign=-1], [AccountTotal="NETPROFIT", AccountType="SUPERANNUATIONEXPENSE", DrCrSign=-1], [AccountTotal="NETPROFIT", AccountType="DEPRECIATN", DrCrSign=-1], [AccountTotal="NETPROFIT", AccountType="AMORTISATION", DrCrSign=-1], [AccountTotal="NETPROFIT", AccountType="INTERESTINCOME", DrCrSign=1], [AccountTotal="NETPROFIT", AccountType="INTERESTEXPENSE", DrCrSign=-1], [AccountTotal="NETPROFIT", AccountType="TAX", DrCrSign=-1], [AccountTotal="ASSETS", AccountType="CURRENT", DrCrSign=1], [AccountTotal="ASSETS", AccountType="INVENTORY", DrCrSign=1], [AccountTotal="ASSETS", AccountType="PREPAYMENT", DrCrSign=1], [AccountTotal="ASSETS", AccountType="BANK", DrCrSign=1], [AccountTotal="ASSETS", AccountType="FIXED", DrCrSign=1], [AccountTotal="ASSETS", AccountType="NONCURRENT", DrCrSign=1], [AccountTotal="LIABILITIES", AccountType="CURRLIAB", DrCrSign=1], [AccountTotal="LIABILITIES", AccountType="LIABILITY", DrCrSign=1], [AccountTotal="LIABILITIES", AccountType="TERMLIAB", DrCrSign=1], [AccountTotal="LIABILITIES", AccountType="PAYGLIABILITY", DrCrSign=1], [AccountTotal="LIABILITIES", AccountType="SUPERANNUATIONLIABILITY", DrCrSign=1], [AccountTotal="NETASSETS", AccountType="CURRENT", DrCrSign=1], [AccountTotal="NETASSETS", AccountType="INVENTORY", DrCrSign=1], [AccountTotal="NETASSETS", AccountType="PREPAYMENT", DrCrSign=1], [AccountTotal="NETASSETS", AccountType="BANK", DrCrSign=1], [AccountTotal="NETASSETS", AccountType="FIXED", DrCrSign=1], [AccountTotal="NETASSETS", AccountType="NONCURRENT", DrCrSign=1], [AccountTotal="NETASSETS", AccountType="CURRLIAB", DrCrSign=-1], [AccountTotal="NETASSETS", AccountType="LIABILITY", DrCrSign=-1], [AccountTotal="NETASSETS", AccountType="TERMLIAB", DrCrSign=-1], [AccountTotal="NETASSETS", AccountType="PAYGLIABILITY", DrCrSign=-1], [AccountTotal="NETASSETS", AccountType="SUPERANNUATIONLIABILITY", DrCrSign=-1], [AccountTotal="EQUITY", AccountType="EQUITY", DrCrSign=11] }, type table[AccountTotal=text, AccountType=text, DrCrSign=number] ) in Source
Finally, update the AccountsTypes table to list the newly created types or classifications.
let Source = Table.FromRecords( { [AccountType="REVENUE", AccountTypeName="Revenue"], [AccountType="SALES", AccountTypeName="Sales"], [AccountType="DIRECTCOSTS", AccountTypeName="Direct Costs"], [AccountType="OTHERINCOME", AccountTypeName="Other Income"], [AccountType="EXPENSE", AccountTypeName="Expense"], [AccountType="OVERHEADS", AccountTypeName="Overhead"], [AccountType="DEPRECIATN", AccountTypeName="Depreciation"], [AccountType="AMORTISATION", AccountTypeName="Amortisation"], [AccountType="INTERESTINCOME", AccountTypeName="Interest Income"], [AccountType="INTERESTEXPENSE", AccountTypeName="Interest Expense"], [AccountType="TAX", AccountTypeName="Tax"], [AccountType="WAGESEXPENSE", AccountTypeName="Wage Expense"], [AccountType="SUPERANNUATIONEXPENSE", AccountTypeName="Superannuation Expense"], [AccountType="CURRENT", AccountTypeName="Current Asset"], [AccountType="INVENTORY", AccountTypeName="Inventory"], [AccountType="PREPAYMENT", AccountTypeName="Prepayment"], [AccountType="BANK", AccountTypeName="Bank"], [AccountType="FIXED", AccountTypeName="Fixed"], [AccountType="NONCURRENT", AccountTypeName="Non-Current Asset"], [AccountType="CURRLIAB", AccountTypeName="Current Liability"], [AccountType="LIABILITY", AccountTypeName="Liability"], [AccountType="TERMLIAB", AccountTypeName="Long Term Liability"], [AccountType="PAYGLIABILITY", AccountTypeName="PAYG Liability"], [AccountType="SUPERANNUATIONLIABILITY", AccountTypeName="Superannuation Liability"], [AccountType="EQUITY", AccountTypeName="Equity"] }, type table[AccountType=text, AccountTypeName=text] ) in Source
Step A3. Recategorise accounts as required
The only remaining step is to recategorise the Accounts so that specific accounts are categorised as either AMORTISATION, INTERESTINCOME, INTERESTEXPENSE or TAX.
To do this, update the Accounts power query to apply the following steps. These steps essentially replace the original field with a new field.
- Rename the original Type field to TypeOld.
Doing this first makes the logic easier to follow. - Add a new calculated column called Type that uses an if statement such as:
= if [Code] = "ABC" then "AMORTISATION" else if [Code] = "EFG" then "INTERESTINCOME" else if [Code] = "HIJ" then "INTERESTEXPENSE" else if [Code] = "XYZ" then "TAX" else [TypeOld]
- And finally, remove the TypeOld field.
It is no longer required now that the new field is in place.
Approach B: Create an alternate AccountTotals hierarchy using Excel tables
To create an alternate AccountTotals hierarchy using Excel, you need to add 3 Excel Tables to your work: AccountTotals, AccountTotalsTypes and AccountTypes. The columns must match those provided by OdataLink.
You can then enter additional rows in all three tables to drive the logic you require.
Step B1. Download the AccountsTotals workbook tables
To make things easier to get started, we provide the following workbooks you can download. They provide the 3 tables required.
Download files for Xero
AccountsTotals workbook for Xero.xlsx
Download files for MYOB
AccountsTotals workbook for MYOB.xlsx
Step B2. Modify the AccountsTotals workbook tables
To rename account totals, simply edit the corresponding AccountTotalName in the AccountsTotals table.
To add new sections in your report, you need to:
- Add new rows to the AccountsTotals to provide their name and placement.
- Add new rows to the AccountsTotalsTypes table to provide logic driving which accounts to include.
- Add new rows to the AccountsTypes table to provide the master list of these new categories.
- Edit the Accounts table to recategory the accounts
The example below adds EBITDA and EBIT along with Depreciation, Amortisation, Interest and Tax.
Step B3. Modify the AccountsTotals power queries to use data from the workbook
Next, you will need to update the power queries to pull from this Excel table.
If building reports in Excel, you simply need to add these tables to your report. Then replace the Power Queries in Excel to source the data from these tables.
Download files for Xero
Xero AccountsTotals Excel Table Power Query.txt
Xero AccountsTotalsTypes Excel Table Power Query.txt
Xero AccountsTypes Excel Table Power Query.txt
Download files for MYOB
MYOB AccountsTotals Excel Table Power Query.txt
MYOB AccountsTotalsClassifications Excel Table Power Query.txt
MYOB AccountsClassifications Excel Table Power Query.txt
If building reports in Power BI, we recommend you place the workbook in Sharepoint and pull the tables through to your Power BI dashboard. This approach can also be used for Excel if you prefer keeping the AccountsTotals hierarchy separate.
Download files for Xero
Xero AccountsTotals Sharepoint Power Query.txt
Xero AccountsTotalsTypes Sharepoint Power Query.txt
Xero AccountsTypes Sharepoint Power Query.txt
Download files for MYOB
MYOB AccountsTotals Sharepoint Power Query.txt
MYOB AccountsTotalsClassifications Sharepoint Power Query.txt
MYOB AccountsClassifications Sharepoint Power Query.txt
Step B4. Recategorise accounts as required
The only remaining step is to recategorise the Accounts so that specific accounts are categorised as either AMORTISATION, INTERESTINCOME, INTERESTEXPENSE or TAX.
This can be done by editing the Accounts power query and applying the following steps. These steps essentially replace the original field with a new field.
- Rename the original Type field to TypeOld.
This is done first as it makes things easier to logically follow. - Add a new calculated column called Type that uses an if statement such as:
= if [Code] = "ABC" then "AMORTISATION" else if [Code] = "EFG" then "INTERESTINCOME" else if [Code] = "HIJ" then "INTERESTEXPENSE" else if [Code] = "XYZ" then "TAX" else [TypeOld]
- And finally, remove the TypeOld field.
This original field is no longer required since the new field is in place.
How to create add additional calculations such as GP%
You can also add specific AccountTotals that are driven for specific calculated measures. For example, you can add a Gross Profit % total. This total can then be linked to a specific measure to calculate the values needed.
While this article describes how to add Gross Profit %, the steps are essentially the same for all types of measure-driven totals.
Step 1. Add the Gross Profit % in AccountsTotals
You can add the total in the same manner as described earlier in the article using either Approach A or Approach B.
There is one one slight difference, however. You do not add any data for the AccountsTotalsTypes or AccountsTypes table. You simply need to add the data in the AccountsTotals.
let Source = Table.FromRecords( { ... [AccountTotal="GROSSPROFITPERCENT", AccountTotalName="Gross Profit (%)", IsProfitLoss=true, IsBalanceSheet=false, IsAccountClassification=false, Order=3.1, VarianceImpactSign=1], ... ) in Source
Step 2. Create the specific measures required for calculating Gross Profit %
Once done, add the measures required. We typically recommend breaking the process down into two parts.
- Adding a new measure to calculate the desired value
- Updating existing measures to make use of the calculated measure.
Using this approach, create a new ActualGrossProfitPercent measure.
ActualGrossProfitPercent = CALCULATE(SUMX(AccountsTotalsTypes, CALCULATE(SUM(ProfitAndLoss[Amount])) * AccountsTotalsTypes[DrCrSign]), FILTER(ALL(AccountsTotals), AccountsTotals[AccountTotal] = "GROSSPROFIT")) / CALCULATE(SUMX(AccountsTotalsTypes, CALCULATE(SUM(ProfitAndLoss[Amount])) * AccountsTotalsTypes[DrCrSign]), FILTER(ALL(AccountsTotals), AccountsTotals[AccountTotal] = "INCOME")) * 100
Once created, update existing ActualAmount measures to make use of this value. Note that the DAX measure required for this step is different between Power BI and Excel as Excel does not have the SELECTEDVALUE DAX function.
Power BI DAX measure snippet
... -- the code to calc for gp % IF(SELECTEDVALUE(AccountsTotals[AccountTotal]) = "GROSSPROFITPERCENT", [ActualGrossProfitPercent], ...
Excel DAX measure snippet
... -- the code to calc for gp % IF(HASONEVALUE(AccountsTotals[AccountTotal]), VALUES(AccountsTotals[AccountTotal]), "") = "GROSSPROFITPERCENT", [ActualGrossProfitPercent], ...