Use a mapping table to group accounts in your Power BI dashboard

Tutorials \ Use a mapping table to group accounts in your Power BI dashboard

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 Power BI. For an article that covers Excel, see Use a mapping table to group accounts in your Excel report.

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 Power BI

The first step is to create a mapping table in Power BI. 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 manual Power BI 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 Power BI

To create a basic mapping table, follow these steps.

  1. Select the Home ribbon tab and click Enter Data
  2. Enter Account in the first column, and AccountGroup in the second column.
  3. Start entering all your account numbers in the first column and the name of the mapping in the second column.
  4. Enter AccountsMappings as table name
  5. Click Load to load this data into Power BI

If you ever need to edit the data for this table, you can go to Power Query using the Transform Data button. Then, when in Power Query, you can select the AccountsMappings table, click on the gear icon next to the Source step and edit the data in the dialogue that appears.

Step 2. 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 Modelling ribbon tab and click Manage relationships.
  2. Click the New Relationship button.
  3. For the first table, select the Accounts table and the AccountNumber or Code column.
  4. For the second table, select the AccountsMappings table and the Account column.
  5. Click Save to create the relationship.

Step 3. Add the AccountGroup column to your report.

Next, add the AccountGroup column to your reports.

  1. Select your Matrix table visual
  2. Find the AccountsMappings table and drag and drop the AccountGroup column to the rows section.

Step 4. 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
(SELECTEDVALUE(AccountsTotals[IsAccountClassification]) = FALSE) && (ISFILTERED(Accounts) = TRUE() || ISFILTERED(AccountsTypes) = TRUE()), BLANK(),

Would need to change to this.

--the code blanks out values for profit to not show the breakdown by accounts
(SELECTEDVALUE(AccountsTotals[IsAccountClassification]) = FALSE) && (ISFILTERED(Accounts) = TRUE() || ISFILTERED(AccountsTypes) = TRUE() || ISFILTERED(AccountsMappings) = 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 create a relationship between the AccountGroup field of the AccountsMappings table and the AccountsGroups table.

Thirdly, 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 Table View vertical button on the far left-hand side.
  2. Select the AccountsGroups table.
  3. Highlight the AccountGroup column.
  4. Select the Column Tools ribbon tab, click on the Sort By Column button and select Index.

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
(SELECTEDVALUE(AccountsTotals[IsAccountClassification]) = FALSE) && (ISFILTERED(Accounts) = TRUE() || ISFILTERED(AccountsTypes) = TRUE() || ISFILTERED(AccountsGroups) = 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.