Save content
Have you found this content useful? Use the button above to save it to your profile.
ABC of Power BI: ‘M’ is for Model | accountingweb
iStock_anyaberkut_modelling

ABC of Power BI: ‘M’ is for Model

by

Continuing his ABC of Power BI series, Hugh Johnson outlines the elements of a good model for producing financial statements.

2nd Nov 2022
Save content
Have you found this content useful? Use the button above to save it to your profile.

An important thing to get right for your Power BI financials report is your data model, or “model”. This is the set of tables that make up the dataset in your report, and the relationships between these tables. Everything flows from this. If you have a good model, it will perform well and your reports will be easier to maintain.

Four main table types in your model

There are four main table types that you may use in your data model: fact tables, dimension tables, disconnected tables, and bridge tables. What distinguishes them are the types of data they contain and their relationships with other tables in your model. 

  1. Fact tables

Fact tables contain information about things that have happened, for example transactions such as sales invoices. These tables will typically contain the highest number of records, perhaps even a few million. 

  1. Dimension tables

Dimension tables contain categorical data that you use to filter your fact tables. For example, an “Accounts” table may be used to filter a table of transactions. Dimension tables typically have relatively few rows and often many more columns than fact tables. They contain only one row per primary column of the table, so “Accounts” may be a table of GL accounts with one row per account code. The columns then contain additional information about the primary column, such as in “Accounts” the account type, a description, its hierarchy in a chart of accounts, and its balance. 

A special kind of dimension table in Power BI is a “Dates” table, whose primary column is a date field that you use to filter your transactions by date ranges. The dates field in a “Dates” or “Calendar” table contains a contiguous sequence of unique dates that are used to filter your fact tables. Explicitly setting this table as a “Date Table” in Power BI prevents Power BI from automatically generating additional unnecessary columns for a date hierarchy and enables you to use a range of DAX Time Intelligence functions. (Though not the subject of this article, I recommend against using Time Intelligence functions because without due care they can produce unexpected results and there are robust ways to achieve the desired outcome.)

There should be a one-to-many relationship from your dimension tables to your fact tables. For example, an “Accounts” table may have one record for each general ledger account with each potentially related to many records in a “Journals” table.

  1. Disconnected tables

As the name suggests, disconnected tables have no active relationship with other tables in your model. You use disconnected tables in your report to capture user intent such as to view YTD instead of a selected month, or to show a reforecast amount instead of a budget. In some cases, I create relationships between disconnected tables, but that is just to give a better user experience when selecting different options (for example, if one option is selected then other options may or may not be valid).

  1. Bridge tables

Bridge tables are used in Power BI to resolve the special situations where there is a many-to-many relationship between two tables that you want to connect. Many-to-many relationships in Power BI can produce unexpected results and performance issues, so are best avoided. To illustrate the example of a many-to-many relationship, let’s consider John Lennon and Paul McCartney and whether they played guitar or piano in the Beatles. Since they both played guitar and piano, a relationship between a “Musicians” table and an “Instruments” table would be many-to-many, as shown below.A picture containing graphical user interface

Description automatically generated

As I mentioned, we do not want a many-to-many relationship in our model, and a bridge table gets around this problem by creating a mapping between the two tables so that we just end up with one-to-many relationships.

Table

Description automatically generated

We often have similar problems in financial statements. Consider the simple income statement shown here:

TableDescription automatically generated

 

The relationship between the rows of this table and a list of GL accounts is many-to-many because the Gross Profit line maps to revenue accounts and COGS accounts in an “Accounts” table and coming the other way, revenue accounts and COGS accounts in the “Accounts” table both map to Gross Profit and Revenue or Gross Profit and COGS respectively. A bridge table is one way to resolve this problem.

A very simple financial statement model (with no complexities like Gross Profit)

Now that we have discussed the different table types that we may use, let’s start building our financial statements model. The image below shows perhaps the simplest of all financial statement models.

This model contains:

  • Dimension tables
    • “Dates”
    • “Accounts”
  • Fact table
    • “Journals”

Notes about this simple model

  • “Dates” 
    • Has a one-to-many relationship from Dates[Date] to Journals[Date].
  • Needs to have an earliest date that is equal to or earlier than the earliest “Journals”[Date] value and a maximum date that is at least the greatest of a) the maximum “Journals”[Date] value and b) the same number of days into the future as there are in the past in “Journals”. This last point is to avoid a subtle problem that can produce unexpected results if you are using the DATEADD function and there are not enough future dates in “Dates”.
    • Would normally contain many additional columns, such as fiscal year, quarter, aged period that you may use to categorise each date and apply filters in your report.
  • “Accounts”
    • Has a one-to-many relationship from Accounts[Account] to Journals[Account].
    • Will need an “Accounts”[Balance] column if you want to create a balance sheet. 
    • Would normally include a hierarchy of the accounts structure as well. Typically, this is two or three levels, but I have seen as many as 13!
  • “Journals”
    • Is a table of all your journal lines. At a minimum it needs to contain the general ledger account, transaction date and amount. If there is a very high volume of journals, then these can be aggregated by accounting period with the date value set to the period end so that you can still create a one-to-many relationship from “Dates” to “Journals”.
    • May contain additional columns with references to departments or tracking codes or similar. Other dimension tables would typically have one-to-many relationships with these columns. 
    • Can also contain budgets or reforecasts as “pseudo journals”. In this case you will need an additional column to identify if the journal line is an “actual” or “budget” and so on. 

This structure is, in my opinion, the essential foundation of any financial statement model. Onto this we can start to build some more advanced things. 

Disconnected tables to improve report usability

You use disconnected tables to create a better user experience. Since they are disconnected, they have no direct impact on any of the other tables in your report. You can, however, use them in slicers and take the selected values to modify calculations in your report (by modifying the filter context of that calculation). 

As I explained in ‘D’ is for Date, one of the compelling reasons to use Power BI to produce financial statements is the flexibility to filter your report over any date range you wish. To achieve a very simple point-and-click selection of date ranges that may be of interest, I would nearly always add two disconnected tables “DatesSlicers” and “SwitchPeriod”. These are shown in the image below.

I would use:

  • “DatesSlicers” to select the year and month values
  • “SwitchPeriod” (aka “Analysis Period”) to select the date range such as “Month”, “QTD”, “YTD” or “12 Months”

Other disconnected tables that I would often use in a financial statement report are:

  • “SwitchComparison” (aka “Comparison”) to select whether we are comparing our actuals with Previous Year, Budget, Forecast and so on.
  • “SwitchAggregation” (aka “Aggregation”) to select in a trend visual over a period range whether the value displayed is the cumulative total or period value.
  • “Switch Calculation” (aka “Calculation”) to toggle calculations between absolute values or % of sales.
  • “SwitchDisplayMultiplier” to toggle between showing absolute numbers or numbers in their thousands, or millions.

Some of these disconnected tables are shown in the example income statement below:

Chart, waterfall chartDescription automatically generated

 

A bridge table to map the layout of your statements to the GL accounts

If we consider the income statement matrix shown above, the rows are from a table called “ChartOfAccounts” that defines the income statement layout. If we try to create a direct relationship between this table and “Accounts” or “Journals”, the two lines “Gross Profit” and “EBITDA” would create many-to-many relationships. 

One design option to get around this is to use “ChartOfAccounts” as a disconnected table, read which row we are on and adjust the filter context of the calculation accordingly. This would work but result in unnecessarily complex DAX code that may be difficult to maintain. A more elegant solution would be to create a bridge table between “ChartOfAccounts” and “Accounts” and let the model do the filtering for you instead of complex DAX code. I go into this in more detail in this previous article ‘G’ is for Gross Profit

The resulting model might look like this:

Graphical user interface, diagramDescription automatically generated

  • “ChartOfAccounts” is a dimension table that defines the layout of the Income Statement and Balance Sheet, with whatever summary/KPI lines you want. Note that you would need to handle Cash Flow differently since the classification of cash flows also depends on the transaction type in “Journals”.
  • ‘’BridgeChartOfAccounts” is a bridge table that maps each line from “ChartOfAccounts” to its underlying GL accounts and from each line in “Accounts” to the corresponding row in “ChartOfAccounts”. The result is a model only with one-to-many relationships.

Example DAX code for a base measure for P&L or Balance Sheet Movement

A bridge table like this makes our DAX code relatively straightforward, and a generic base measure to calculate either an actual P&L amount or Balance Sheet movement for all the rows in your statements, except for ratios, could be as simple as this:

Graphical user interface, text, application

Description automatically generated

 

The variable CoALineAmount (lines 2 to 7) sums the Journals[JournalLine_NetAmout] (line 4), using the table filter “BridgeChartOfAccounts” (line 6) to map the row of “ChartOfAccounts” to the correct GL codes in “Accounts”. Line 7 filters “Journals” so that only actuals are included, ignoring budget or reforecast transactions.

Lines 9 to 13 multiply by -1 in the case that the row of “ChartOfAccounts” is part of the income statement or leaves it as is in the case that it is part of the balance sheet.

You can then filter this [BaseAmount] measure by the required date range to get the result that you need. As a side comment, I find that separating the dates filters from the base amount calculations in this way makes the report more maintainable.

Handling the slightly more complex scenario of ratios like Gross Margin %

You can use a bridge table also to manage the mapping of ratios, but with the numerator and denominator handled separately. Ratios must be managed in a slightly more sophisticated way by mapping the numerator and the denominator separately. This makes for a slightly more complex [BaseAmount] calculation, but is still preferable (for performance and maintainability reasons) to explicitly creating exception DAX code for each ratio. One formula like the one below will compute a base P&L amount or Balance Sheet movement (excluding date filters) for all rows of the financial statement:

In this new scenario we have added an additional BridgeChartOfAccounts[FractionPart] column to identify for each row if it is mapping the numerator or denominator. “ChartOfAccounts” lines that are not ratios are mapped as “numerator” and a new column ChartOfAccounts[Calculation] identifies if the row is a regular sum or a ratio.

Lines 2 to 10 calculate the numerator. This is precalculated as variable because we will need this result in both branches of the measure.

Line 13 tests to see if the row of “ChartOfAccounts” is a ratio, in which case it executes lines 14 to 24, otherwise it returns the variable value for the numerator that we have already calculated.

Finally, lines 28 to 32 switch the sign of the result, depending on whether this is an income statement or balance sheet calculation.

Additional dimension tables to slice and dice your report 

With a strong model in place like the one above, it is very easy to add additional dimension tables to slice, dice and filter your report. The only condition is that you are looking for is a one-to-many relationship between any additional dimension table and “Journals”. This could be departments, customers, suppliers, projects, tracking categories and so on.

You can simply drag and drop fields from the dimension tables into slicers or other visuals to create filters and summaries by dimension.

The diagram above shows “Contacts” (aka Customers and Suppliers) and “TrackingCategories”.

Note one subtle thing: this model is based on Xero data. In the Xero data model, there is not a direct relationship between “Contacts” and “Journals”, so you would have to do some work in Power Query to add the Contacts.ContactId field to “Journals”.

Additional tips and notes to optimise performance

I will go into the subject of optimising the performance of your financial statements model in a future article, “ABC for Power BI: P is for Performance”, but meanwhile here are some simple tips:

  1. Flatten the hierarchy. Try to avoid situations where you have dimension tables filtering other dimension tables in some kind of hierarchy before you reach the fact table(s). This kills performance. If you have this situation, try to flatten the hierarchy into just one level above any fact table.
  2. Minimise the number of columns in your fact table(s), particularly columns that have a high number of unique values.
  3. Append similar fact tables into one longer table – for example, have a single “Journals” table instead of separate tables for “Journals2022”, “Journals2021”, “Budget2022”, “Budget2021” and so on. This will simplify your model, make your DAX code easier to write and improve overall performance. For financial statements you can usually reduce your model to a single “Journals” table. A model to support more detailed analysis of product sales, for example, may require additional fact tables.
  4. Remove all columns that you are not using (particularly in your fact tables).
  5. Remove old rows in your fact tables that you are never going to be of interest; do you still want to report your income statement from five years ago, with the ability to drill down to each individual journal?
  6. In a situation where you have a very high volume of journal lines, you can pre-aggregate them by accounting period, or even do a hybrid where recent journal lines are not pre-aggregated but older ones are. This will dramatically reduce the size of your “Journals” table, but you will lose the ability to drill down to the detail of any aggregated journals lines. If you do this, set the Journals[Date] value for each aggregated record to the end date of the aggregation so that you can continue to use “Dates” as a regular date table in Power BI.

Summary

Getting your model right is probably the single most important step in producing flexible, maintainable financial statement reports that perform well. Every hour that you spend getting your model optimised will save you multiples of this downstream. This article is just an introduction to a topic that may have many more subtleties in certain circumstances. 

Useful links

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.