Use a mapping table to group accounts in your Excel report

Tutorials \ Use a mapping table to group accounts in your Excel report

A common requirement when designing Profit and Loss and Balance Sheet reports is the need to group your accounts together to create meaningful subtotals.

This article will provide the steps necessary to create and apply this mapping table for reports built using Excel. For an article that covers Power BI, see Use a mapping table to group accounts in your Power BI dashboard.

We’ve used a Xero chart of accounts as source data but the concept can be applied to other sources like MYOB.

Create a Basic Account Mapping table in Excel

A mapping table is a simple concept that provides additional data.

At it’s core, it simply needs to have two columns:

  1. The account number, name or code to identify the account.
    We recommend using the account number or code. Ultimately, you want to be able to uniquely identify an account. For more advice, see considerations when consolidating data from multiple data files.
  2. The name of the mapping that will be used to group accounts together.
    If you need more levels to group accounts, you simply need to add additional columns.

We recommend using a standard Excel table to capture this information as it provides an easy way to enter and maintain the data. But you can use other approaches.

Note that there are more advanced ways to create the mapping table which is covered at the bottom of this article.

Also worth noting you can download our Account Mapping Table example that this article references.

Step 1. Create the mapping table in Excel

The first step is to create a mapping table in Excel. To create a basic mapping table, follow these steps.

  1. Add a new sheet to your workbook.
  2. Rename the sheet to AccountsMappings.
  3. Select the two cells in the sheet, select the Insert in the ribbon and click on Table.
  4. Tick the option My table has headers.
  5. Select the Table Design ribbon tab and enter AccountsMappings as table name.
  6. Enter Account in the first column, and AccountGroup in the second column.
  7. Highlight the two columns, select the Home tab and choose to format them as Text.
    This ensures that as you entered accounts starting with Zero (0) or containing dashes (-), that Excel will not interpret this data as a number or date.
  8. Start entering all your account numbers in the first column and the name of the mapping in the second column.

Step 2. Add this mapping table to your data model

Once the table is created and available Excel, you need to add this table to your data model.

  1. Select any cell within your table.
  2. Select the Power Pivot ribbon tab and click Add to Data Model.

Step 3. Add relationships between your Accounts table and your AccountsMappings table.

Once the table has been added to the data model, you will need to setup the relationship between the Accounts table and the newly created AccountsMappings table.

  1. Select the Power Pivot ribbon tab and click Manage.
  2. Select the Design ribbon tab and click Manage Relationships.
  3. Click the Create button.
  4. For the first table, select the Accounts table and the AccountNumber or Code column.
  5. For the second table, select the AccountsMappings table and the Account column.
  6. Click OK to create the relationship.

Step 4. Add the AccountGroup column to your report.

Next, add the AccountGroup column to your reports.

  1. Go to the pivot table report to add the column.
  2. In the Pivot Table Fields section, select the All tab
  3. Find the AccountsMappings table and drag and drop the AccountGroup column to the rows section.
  4. If you need to include subtotals, edit the pivot field and choose Automatic subtotals.

Step 5. Adjust measures to calculate correctly if needed.

Finally, you may also need to adjust measures.

In our standard templates, we designed the measures such that Gross Profit and Net Profit do not show breakdown by accounts.

To preserve this mechanism, you might also need to edit the measures so that they do not calculate the breakdown values for the new AccountGroup column.

Specifically, DAX measure snippets that looks like this.

--the code blanks out values for profit to not show the breakdown by accounts
IF(HASONEVALUE(AccountsTotals[IsAccountClassification]), VALUES(AccountsTotals[IsAccountClassification]), TRUE) = FALSE && (ISFILTERED(Accounts[CodeAndName]) = TRUE() || ISFILTERED(AccountsTypes[AccountTypeName]) = TRUE()), BLANK(),

Would need to change to this.

--the code blanks out values for profit to not show the breakdown by accounts
IF(HASONEVALUE(AccountsTotals[IsAccountClassification]), VALUES(AccountsTotals[IsAccountClassification]), TRUE) = FALSE && (ISFILTERED(Accounts[CodeAndName]) = TRUE() || ISFILTERED(AccountsTypes[AccountTypeName]) = TRUE() || ISFILTERED(AccountsMappings[AccountGroup]) = TRUE()), BLANK(),

This is not a thorough explanation on the EXACT DAX required as the complexity depends on the DAX formulas in use. It is important to be aware of it, however.

Advanced Mapping Tables Concept

Controlling the order of the groupings

A common requirement is to control the order of the grouping since by default they would be sorted alphabetically. For instance, you may want to ensure that the Other grouping always appear at the bottom.

The steps to achieve this are very similar to creating the basic mapping table.

First, you will need to create another table representing the unique names of all the groupings. In this table, you will want two columns:

  1. The AccountGroup name
  2. An Index column

We recommend you name this table AccountsGroups and rename the sheet to match.

Secondly, you will want to add this table to the data model in the same way you added the AccountsMappings table.

Thirdly, you will want to create a relationship between the AccountGroup field of the AccountsMappings table and the AccountsGroups table.

Fourth, you will want to make the AccountGroup field of the AccountsGroups table be sorted by the Index column. To do this, follow these steps.

  1. Select the Power Pivot ribbon tab and click Manage.
  2. Select the AccountsGroups table.
  3. Highlight the AccountGroup column.
  4. Select the Home ribbon tab and click on the Sort By Column button.
  5. Select the AccountGroup column in the Sort frame
  6. Select the Index column in the By frame
  7. Click OK to save the changes.

Fifth, you will want to make small adjustments to the measures.

--the code blanks out values for profit to not show the breakdown by accounts
IF(HASONEVALUE(AccountsTotals[IsAccountClassification]), VALUES(AccountsTotals[IsAccountClassification]), TRUE) = FALSE && (ISFILTERED(Accounts[CodeAndName]) = TRUE() || ISFILTERED(AccountsTypes[AccountTypeName]) = TRUE() || ISFILTERED(AccountsGroups[AccountGroup]) = TRUE()), BLANK(),

Finally, you will want to update the swap the AccountGroup field so that it uses the one from the AccountsGroups table.

Considerations when consolidating data from multiple data files

When creating a mapping table that applies to multiple companies, organisations or data files, there are some things to consider.

In some situations, using the account code or account number as key for the relationship is not ideal. This is the case if the chart of accounts are vastly different and have been coded or numbered independently. For example, account 200 could represent revenue in one data file, but travel expenses in another and legal expenses in yet another.

In those situations, it may be better building the relationship based on the account name. This is because the account named Sales or Bank Fees is likely to mean the same thing across multiple data files.

In other situations, it may in fact be better using the internal account database ID field (AccountUID / AccountID) as these generally uniquely identify the account. The issue with this approach, however, is that it may make it more difficult to trace through.

Another option is to add an additional column for the company, organisation or data file name. And creating a composite key that concatenates this company name with the account code to unique identify a single account for a specific data file.