Here are the things required to make the most of this chapter:

• Chapter files: You will find the datasets and Power BI files used in this chapter in the book’s official GitHub repository here: https://github.com/PacktPublishing/Power-BI-for-Finance.
• Software requirements: Most examples covered in this book require only the free Power BI Desktop version, but I strongly recommend Pro to profit from Power BI service features like publishing reports on the web.
• Hardware requirements: As Power BI makes heavy use of local memory in Power BI Desktop, my recommendation is to have as much RAM as possible—at a minimum, 16 GB. My work desktop works well with 48 GB.

With CPU, my recommendation is at least 8 core processors with 3+ GHz clock speed; 16/24 cores will give you even better results. With screen resolution, again, the more the merrier to consistently enable a full, detailed view of your dashboards, even when multiple side panes are displayed. I am using a resolution of 3,840 x 2,160 pixels on my work desktop, but 2,560 x 1,440 and a dedicated GPU should suffice for most requirements.

Since 2015, Microsoft’s Power BI platform has grown to be the leading solution for data analytics.

I assume that you are familiar with the key concepts of the Power BI platform, so here is just a brief overview of its components. The Power BI platform comprises the following:

• Power BI Desktop app: This is a free development environment. It enables analysts to build data models, logic, interactive reports, and dashboards. The data in a Power BI model is either preprocessed and stored in Microsoft’s proprietary Analysis Services format (Import mode) or queried at runtime (Direct Query mode) from supported data storage technologies such as Azure/SQL Server, SAP HANA, and others. Power BI includes its own calculation language called Data Analysis Expressions (DAX), which enables users to define calculations that are stored in the model (PBIX file or published to the Power BI service).
• Power Query: This is a sub-application integrated in Power BI Desktop where power users can integrate and transform data from different data sources into a composite data model optimized for analytical purposes. The definition of the transformation steps is stored in a format called M. The relevant M statements are either automatically generated by the Power Query UI or can be manually maintained by users using the M syntax.
• Power BI visuals: These are an extensible set of visualization options that are included in Power BI Desktop or can be added from a commercial marketplace. The Power BI visuals are resizable objects that can be positioned on a Power BI report or dashboard to display the required data (fields of the data model) in the desired format, such as tables, charts or diagrams, indicator cards, and so on. Beyond these core options, specialized visuals also support advanced functionality, including artificial intelligence features like key influencer detection or regression analysis.
• The Power BI service: A cloud-based application where a power user can publish a report from Power BI Desktop for regulated (restricted by access rights) consumption with a web browser. Alternatively, a Power BI Desktop report can also be shared in the Power BI Desktop .pbix file format. Access to the data in this file is then only restricted by access to the underlying data sources. The detailed user rights to particular granularities in the data model (for example, a specific scenario, product group, or legal entity) that can be set in the Power BI service (row-level security, or RLS) do not apply in this case on Power BI Desktop. Besides report distribution, the Power BI service also fulfills a variety of data governance tasks like user group definitions, data access and security maintenance, administration of data sources, and the like.

Next, let’s see how we can expand the capabilities of Power BI even further by using Microsoft Fabric.

Microsoft Fabric: enhancing Power BI

In 2023, Microsoft launched Microsoft Fabric as an integrated analytics platform that builds upon the capabilities of Power BI and extends it with additional tools and functionalities designed to provide a more comprehensive solution for data management, analytics, and business intelligence. Microsoft Fabric combines the power of Power BI, Azure Synapse Analytics, and Azure Data Factory, offering a unified environment for data processing, storage, and visualization.

Here are the key benefits of Microsoft Fabric:

• More scalable data processing: Microsoft Fabric leverages the power of Azure Synapse Analytics, offering high-performance data processing and storage capabilities. This scalability allows organizations to handle large volumes of data and complex queries, which is particularly important for financial applications that require real-time insights and rapid decision-making.
• Enhanced security and compliance: Microsoft Fabric includes robust security features, such as data encryption, access controls, and compliance monitoring, ensuring that sensitive financial data is protected. This is crucial for financial applications that must adhere to strict regulatory requirements.
• Enhanced scenario planning: The advanced analytics tools available in Microsoft Fabric, such as predictive modeling and machine learning, enable finance teams to perform more sophisticated scenario planning and forecasting. This helps organizations to better anticipate future trends and make more strategic decisions.
• Streamlined data governance: Microsoft Fabric’s integrated environment simplifies data governance by providing a single platform for managing data access, security, and compliance. This ensures that financial data is handled in a consistent and secure manner, reducing the risk of data breaches and regulatory violations.

Now that we are familiar with the key components and concepts of Power BI, we will review how they can add value for particular finance team tasks.

Building calculation models and presenting data are core requirements for finance users. The standard tool of choice for these purposes is typically a spreadsheet solution like MS Excel or Google Sheets, which enables users to build tabular business models and charts.

Spreadsheets are great for flexible analysis where users require completely flexible cell-based layouts with limited data (typically less than a million rows that can be handled in a spreadsheet) but have limitations when larger amounts of data or several different data sources are involved. Let’s see how they compare with a solution like Power BI.

Spreadsheets versus Power BI

The following diagram outlines the differences between working with data stored in spreadsheets as opposed to separating the presentation (spreadsheet tables and charts) and data storage layer (spreadsheet that presents data stored in a separate database):

Figure 1.1 – Separation of data and presentation

In a spreadsheet, there is typically no separation of data from the presentation, as opposed to an approach where the data resides separately in a database with a standardized data structure optimized for handling high volumes of transactions that are also accessible with other tools.

The spreadsheet method leads to a variety of very severe challenges for ongoing, standardized reporting and data collection requirements (such as budgeting and planning):

• Workbook proliferation: New or updated data typically means an additional spreadsheet, which very quickly leads to a proliferation of spreadsheet files (for example, Budget 2022 Version 1 to Version n), which becomes very hard to control.
• Access rights granularity: No granular access rights can be assigned; a user can either see the full spreadsheet or nothing. Allowing a user access to specific parts of the data (for example, data for a particular company, product, or scenario) is impossible or very cumbersome to realize.
• Processing performance: Spreadsheet processes are typically slow as they run in a decentralized manner; a spreadsheet file is sent to another business unit for offline data collection. Once finished, it is sent back and then must be integrated into the overall model with data from all other business units. The user can’t see how their entries will affect the total business, considering the last version of data from all other decentralized entities in real time.
• Limited interactivity and analysis options: Spreadsheets include great charting and dynamic matrix analysis (Pivot Tables) capabilities but lack more advanced business intelligence features available in specialized applications, such as complex data filtering, dynamic navigation within hierarchies, cross-filtering and drill through between different tables and charts, artificial intelligence/machine learning capabilities, and so on.

In contrast, Power BI offers the following :

• Real-time access: Power BI offers live (i.e., automatically updating) access to a nearly unlimited number of data sources and provides a platform to transform these disparate sources into an integrated business data model.
• Filtered views and custom aggregations: In contrast to several spreadsheets in a single file/cloud application, where users only get access to the data details, Power BI offers the option to use filters to retrieve any combination of calculated, aggregated results from within the entire underlying data set, such as a point in time or a time span, a scenario, a legal entity, or whatever “dimension” criteria was defined in your underlying model.
• Unparalleled interactive analytics capabilities: This includes drill down on a table/chart (navigation into further detail), drill through (navigation to a different page or a more detailed report), and cross filtering (a selection on one chart/table is applied to others), in addition to very strong inbuilt visualization capabilities that can be enriched with a vast array of extensions and additional application options available in a commercial marketplace.
• Two-way operations/write-back: Power BI on its own has limited options for data collection and data write-back, but it lets us add these with extensions like Power Apps, or specialized applications from the extended Power BI ecosystem, such as Dynamics Business Performance Planning and the related Acterys offering that are also covered in this book. The best source to review and obtain these solutions is Microsoft’s AppSource marketplace (https://appsource.microsoft.com/).
• Dedicated application and web browser support: Power BI reports that are published on the Power BI service only require a standard web browser for consumption. There is no need for users to install a specific spreadsheet solution that might not run on their platform, such as Microsoft Excel in Linux environments. This is admittedly less of an issue in recent years, as many spreadsheet solutions now offer browser-based access.

Conclusion

So, does Power BI replace spreadsheets completely?

Despite all the advantages we’ve discussed here, Power BI will not replace spreadsheet solutions completely. It is not suitable for one-off (as opposed to ongoing repeating) scenarios with limited data that require extensive flexibility regarding the tabular layout, such as layouts that can’t be addressed in one or more standard tables with a symmetric row/column layout. This includes complex merger and acquisition transactions, feasibility and project investment analyses, response forms, and similar requirements that are highly varied.

Given the right approach (which you will learn from this book), Power BI offers significant efficiencies for recurring, standardized finance requirements like ongoing financial (income/balance sheet/cash flow statements) or operational reporting, budgeting and planning processes, and any scenario that requires more advanced visualizations and data analysis capabilities with, for example, machine learning features.

With that established, let’s explore some of these Power BI features, starting with simple data models.

The data model is an abstraction layer that acts as an effective interface between the analyst and the raw data and is a key pillar for realizing your financial requirements in Power BI.

If your data model is not properly defined, every following step, such as defining calculation logic, report layout, and charts, will lead to unnecessarily inflated build times and limited usability for your users. In some cases, a wrong data model might even prevent you from realizing a reporting or calculation requirement at all. As seen in Figure 1.1, the data model integrates the data sources that you intend to use and structures how your users can access and use the data for reporting.

The optimal overall structure for a data model, unless you are working with a single, very simple file, is the star schema. Let’s dive into that next.

Understanding the star schema approach

This approach breaks up the model into a central fact table with the transactions and several dimension tables with the master data details. This structure is typically applied in a data warehouse where data from different sources is consolidated into a database for analytical purposes. This same principle applies equally to Power BI, which incorporates a similar role as a centralized analytical model layer.

A star schema separates transactional data and analysis dimensions and leads to optimal performance and ease of analysis for your users. These dimension tables include all details like hierarchy relationships or attributes of a particular analysis perspective. For example, dimensions that are commonly used to analyze journal transactions of an enterprise resource planning (ERP)/accounting system typically include the following:

• Accounts
• Organizational entity
• Scenario
• Time

The dimensions are connected to a fact table that contains the actual transactions, as outlined in the following figure:

Figure 1.2 – Financial star schema

Ideally, the connection between fact and dimension tables is implemented via unique (integer) IDs. On the one hand, this provides the best performance for analysis, and on the other hand, it facilitates changes. For example, if you want to change the name of an account (and the ID stays the same), you only have to modify the account name in the dimension’s Name attribute without the need to apply that change to every single journal entry that is in the fact table for that account.

This model enables users to create any possible report layout by adding row and column item hierarchies and filters, as displayed in the following diagram:

Figure 1.3 – Report based on star schema model

Several star schemata can be combined to create a galaxy schema, which is a data model that links multiple fact tables through shared dimension tables. But first things first. In the following section, we will cover how you can build an initial star schema for financial purposes in Power BI.

Building a star schema financial model in Power BI

In this section, you will learn how to build a financial star schema model based on sample data that is very similar in structure to a typical accounting/ERP system. The sample file is in Excel format, which is normally supported as an export option in any major accounting system.

Now, you might ask, why are we not connecting to the accounting system directly? This is, in most cases, impossible or not advisable, as data structures in the source system are not set up for analysis purposes and direct access involves complex API authentications that require expert IT skills. A way around is a separate data warehouse where data is automatically loaded from the accounting system (and other sources) in a structure optimized for analysis. Some accounting system vendors offer this as a feature. In addition, there are solutions from software vendors that offer data warehouse creation in the cloud from accounting/customer relationship management (CRM)/billing systems that are directly accessible from Power BI as a service.

Using an Excel file is a very quick way to create a data model prototype or showcase data model building, but it involves ongoing manual efforts to create the files. For our purposes, it is the easiest way to demonstrate this. In productive systems, you wouldn’t use data extracted from an ERP system but would directly connect/import data from a data warehouse or respectively prepared star/galaxy schema defined in a SQL data source. The sample Accounting Data Set.xlsx dataset is available for download in the book’s GitHub repository. We will discuss this in the following section.

Loading Excel data through Power Query

To go through this example, you will require a current Power BI Desktop application installed on your computer. You can download the latest version of Power BI Desktop free of charge from Microsoft’s current download area: https://www.microsoft.com/en-us/power-platform/products/power-bi/desktop. The first step is to connect the data file to your Power BI Desktop report. This is done through the Power Query data integration component that is part of the Power BI Desktop application. The easiest way to load an Excel file is from the main ribbon:

Figure 1.4 – Source selection

From here, you point to a file, and this dialog will show where you select the following four marked tables:

Figure 1.5 – Table load

These tables are essentially components of the star schema that we have covered at the beginning of this chapter. Transactions is the fact table that contains sample accounting journal transactions, and Accounts, Calendar, and Organisation are related dimension tables. Once you have selected the tables, click on Transform Data. Unlike the Load button, this gives you an option to edit the single steps of the loading process. Load would immediately load the data otherwise. After you have clicked Transform Data, you will be in the Power Query environment and see a dialog similar to the following one:

Figure 1.6 – Power Query tables and steps

What we see here now are our four tables on the left, a sample of the currently active data table in the middle, and transformation steps for that table on the right. The latter is the area where you will work to add and edit transformations of your data tables.

The Accounts table contains all the details of a typical accounting chart of accounts, such as account name, code, account groupings, and many more. What is important with all tables is that they have a common denominator, or in other words, a common column to connect dimension tables with the fact tables. This is normally defined on the lowest granularity of the fact table and connected to the specific ID of the respective account in the Accounts table, here based on an integer ID. This approach typically gives you the best performance in Power BI (likewise in a data warehouse). If your data tables don’t have these IDs already in place, you can also use a common string or add an integer ID applying Power Query commands and/or scripting. As covering Power Query in detail is not the subject of this book, you can refer to the following great training materials provided by Microsoft and the Power BI community on how you can accomplish this:

• https://powerbi.microsoft.com/en-us/learning/
• https://community.powerbi.com/
• https://www.youtube.com/@yourcfoguy
• https://www.youtube.com/@PowerQuery

All tables in this sample are already structured as we need them, so the only task left is to check whether the necessary relationships between the tables are in place. For this purpose, we can switch back to the Power BI Desktop app (we can leave the Power Query dialog still open) and switch to the Model section:

Figure 1.7 – An overview of table relationships

Here, we see our four tables, with three of them that Power BI has automatically added a relationship to. For the Calendar table, we’re obliged to do this ourselves: just drag the Date field from the Calendar table to the Transactions table or use the Manage relationships button in the top ribbon to achieve the same in the following way. First, create a new relationship by clicking on the New… button:

Figure 1.8 – Creating a new relationship

Then, select both the Calendar and the Transactions table and select (tick box) the Date column in each:

Figure 1.9 – Relationship definition

As a result, you will see the new relationship in place:

Figure 1.10 – Star schema data model

This sequence of settings was all required to establish an easy-to-use initial data model that will give us the ideal basis to build on for future calculation logic, reports, and visualization examples.

Now that we have a basic data model in place, we can build our first financial report with it.

Building a simple financial report with a star schema

Here are the steps you can follow to build a financial report with the star schema.

To get started, switch to the Report view section in the Power BI application.

Figure 1.11 – Power BI Report View

From here, we can now add visuals as the building blocks of our report. Among the default visuals included in Power BI, a good choice as the foundation of a financial report is the Matrix visual. Just click on the respective symbol of the visual you intend to use in the Visualizations pane (see Figure 1.12) on the right, and it will add an initially empty container to your report canvas.

With our new Matrix visual, we can now define a report that shows a two-level (Type and Name columns) account hierarchy in the rows of the matrix as well as in the three consecutive months in the columns. In addition, we can add two Filter visuals at the top for which we assign Financial Year Quarter and LegalName columns from the Calendar and Organisation tables, respectively.

As a final step, we can drag a Text Box object from the Insert tab on the ribbon to the report canvas for displaying the report title. The result will look similar to this:

Figure 1.12 – Power BI report elements

This was a quick start, but the report obviously has a few shortcomings, and we can improve it further. Let’s assume we only want to show an income statement here with the related accounts. This means we need to filter out balance sheet accounts (like the BANK entry) that are currently displayed in the report.

To accomplish this, we select the Matrix visual, uncheck Select all for the account types (the Type column) and then just select the five income statement categories (DirectCosts, Expense, OtherIncome, Overheads and Revenue) in the filter section:

Figure 1.13 – Visual filters

A smarter option to filter only the income statement accounts would be to enhance the Accounts table by adding a new Statement column; for example, add PL for all income statement (Profit & Loss) accounts and then filter the Matrix visual on that column. We can either create this column directly in the underlying Excel file or create a rule based on the account type. In our case, this is straightforward: account types with an order less than 7 represent Profit & Loss (PL) accounts, and types with an order of 7 or higher represent Balance Sheet (BS) accounts. So, we simply add a custom column to assign PL to rows where the type order is less than 7, and BS to all others.

This approach has the advantage that as long as we categorize them correctly with the PL attribute, new account groups will automatically appear in the report without us having to modify filter item selection settings.

Another issue that we still have is that the order of items in the income statement is not correct. Revenue should be at the top, and all other account groups should be listed in the correct order for a financial report. This is easy to achieve in our sample as we have a column called Type Order that specifies the sequence in which we want to display the financial report items. So, we only must apply an “order by” reference to the model. We can do this by clicking on the Type column in the Accounts table and then selecting Sort by column from the Column tools tab in the ribbon, and pointing to the Type Order column:

Figure 1.14 – Sort options

This now gives us a properly structured report with the account groups in the correct order:

Figure 1.15 – Power BI report

To display all groups in the report, you have to switch to All Quarters in the FinancialQuarter filter visual as some quarters only have data for some accounts along the dimension hierarchies. Be aware, you potentially have to activate Select all in the filter properties before getting that option.

Next, we can add a sparkline and data bars to the report to facilitate analysis. Just select Add a sparkline in the context menu in the Sum of Balance column in the Values section (click on the down arrow):

Figure 1.16 – Adding a sparkline

In the next step, we select Year_Month from the Calendar table (this would work equally with Date, but this can result in too many data points to process for Power BI). To add the data bar, we click again on the Sum of Balance context, choose Conditional formatting, and then Data bars:

Figure 1.17 – Conditional formatting

Change the color of the negative bar to a reddish tone to clearly outline the difference to positive values:

Figure 1.18 – Positive/negative colors

As a final touch, we can change the Subtotal label at the bottom from Total to EBIT (Earnings Before Interest and Tax) in the Visual properties section:

Figure 1.19 – Custom subtotal name

This change of name for the total is correct as we have revenue and cost with positive and negative signs (a contentious approach that some accountants might not like, but for simplicity in this example, hopefully is permissible):

That is it! We now have a dynamic financial report that only took us a few minutes to create:

Figure 1.20 – Final report layout

This, though, is a very simplified approach that doesn’t incorporate custom subtotal calculations, such as calculating a gross profit and others. Also, more advanced formatting options (for example, row-specific number formats and line handling) are not taken into account for now. We will cover all those in the dedicated Financial Reporting chapter (Chapter 4).

In the following section, we will review how we can amend the current model based on actuals with additional plan data.

Adding scenario dimensions and budget/plan data

A common requirement in finance is to compare your actuals with plan-related data, for example, an annual budget that we can add to our data model. In our current sample and in most other cases, the data for the plan will reside in a separate system, typically spreadsheets or a specialized planning software solution. In this section, I will cover the optimal modeling approach for integrating this type of data.

The typical challenge here is that the plan data is not available in the same granularity as the actuals from your accounting system. This typically involves the following:

• The time detail (e.g., budget by month versus date-specific transactions in your ERP system)
• The accounts (budgets are not done on the detailed chart of accounts)

In the following example, I will cover a typical scenario where budget data is available on a monthly basis but otherwise matches the actuals. Here, we assume that budgets were already prepared in the accounting chart of accounts structure. In a scenario where this is not the case, the options are either to manually map budget accounts in the source or introduce relationships on a different level than the lowest granularity (e.g., on account group level). This, though, can get relatively complex. Marco Russo covers this case very well in this article: https://www.sqlbi.com/articles/budget-and-other-data-at-different-granularities-in-powerpivot/.

Here, we will only cover a simpler example where only the date hierarchies don’t match. For that, we have the same options as with Accounts, where we added an additional attribute for account grouping. The quickest and easiest way is to just introduce a match on the lowest granularity level, or in other words, add a Date column on the same detail as our Calendar table. We can do this either in Excel (which is very easy to accomplish for finance pros who are likely experts with that solution) or in Power Query.

The file that contains our budget numbers is called Budget Sample.xlsx. We can load that the same way as in the previous example by using the Excel workbook button. Once we load that in Power Query, we see that in this file, the headers were not automatically recognized. So, as a first step, we need to specify that we want to use the first row as headers:

Figure 1.21 – First row as header

In the next steps, we need to make sure we can link the Budget table with the main Actuals one. The only missing key (the ID column) here is the Date key. While the other fields in the Budget table can be matched to dimensions already present in the Actuals table, the Budget table currently lacks a proper Date field—it only contains separate year and month values. An easy way to accomplish this (without complex DAX) is to just transform the existing date information into a Date field. Power Query is a good way to go: we just add a new column that concatenates the first day of the month number (1), the month, and the year. We can add a new custom column in Power Query from the Add Column menu:

Figure 1.22 – Adding a custom column

From here, we can now add a new column named Date and enter the definition of the column. What helps us here is the M function, Date.FromText, which converts a text string into a date value. So, we concatenate the time information in focus and add 1 for the first day of the month:

Figure 1.23 – Adding M calculation

When we press the OK button, we unfortunately run into an error:

Figure 1.24 – Column errors

Clicking on the error text, we get the following explanation:

Figure 1.25 – Error details

The reason for this error is that the Year and Month columns are not specifically defined as Text types. We can easily change that by selecting the two columns (multi-column select requires holding the Ctrl key here) and assigning them the data type of Text:

Figure 1.26 – Changing the column type

Now, we see that this operation was added to the end of the list of operations in the APPLIED STEPS pane, which doesn’t help us, as this transformation needs to happen before the Column Add action. We can address this by dragging Changed Type before the Added Custom step:

Figure 1.27 – Step sequence change

After that, we see that our added column shows the date correctly. There is just one step missing: we need to change the format to a date to correspond to our Calendar and fact tables. We can easily do that by selecting the column and changing that to Date in the Data Type definition in the ribbon:

Figure 1.28 – Date type column

Now that we have the table in a format at the same granularity, the best option is to merge the Actuals table with our Budget table into a single combined (fact) table. Another option would be to use two separate fact tables. But if there is an option to bring the two tables into the same granularity for all dimensions (differences in fact table columns normally don’t matter and can be handled), I would always recommend a single fact table as it makes handling both, calculation logic and reporting, much easier and less complicated to maintain.

If you really want to use multiple fact tables for scenarios, it is imperative that all dimensions have relationships with all these fact tables. Otherwise, any report that uses measures from multiple tables will work incorrectly. That’s because a filter in a dimension that is only used in one fact table and wouldn’t apply to the other ones. This makes the results incomparable. In the following section, we will look at how this can be done.

Merging Actuals and Budget tables

As explained before, we need to merge tables to facilitate calculations and keep our data model as simple as possible. Specifically, we need to merge our new Budget table into our existing Actuals table. In order to keep our existing reports working right, we need to ensure that we handle scenario filters correctly, by either filtering these to just Actuals or adding a new Scenario dimension to the report for displaying multiple scenarios. Let’s get started with the following steps:

1. First, we need to modify the structures of our two fact tables and add a new Scenario column with the Power Query Editor. Let’s start with the Transactions table where we add a custom column by clicking the Custom Column button in the Add Column ribbon. In that new column, we can now assign every record with the Actual scenario by typing ="Actual":

Figure 1.29 – Custom Column formula

We do the same for the Budget table but assign "Budget" in this case:

Figure 1.30 – Budget custom column

There is an argument to use IDs for the scenarios as they will be processed faster when the filters from a dimension table are applied. But this would have no measurable effect in our model and is typically not an issue in financial models, as those in most cases don’t comprise very large amounts of data (i.e., exceeding 100m records).

2. To merge the two tables effectively into each other, we need to make sure that the main Value fields in the two columns match. In our example we assume that the actual values come as the net amount in group currency (GC) represented by the NetAmount_GC field in the Transactions table. So, we rename the GC field in the Budget table to the same by double clicking and typing NetAmount_GC:

Figure 1.31 – Merging tables

These steps enable us now to merge the two tables into one by appending the records from the Budget table to the actual Transactions table:

3. To accomplish we have only to select the Transactions table and then select Append Queries from the ribbon.
4. In the following dialog box we select the Budget table:

Figure 1.32 – Append table

Now, we see that our Transactions table includes the Budget records as well:

Figure 1.33 – Populated custom column

Theoretically, we could work with that table and use the new Scenario field from the Transactions table. Apart from the fact that it’s not good practice from a data model perspective, this will slow filtering (slicing) performance down as applying a filter directly on the Scenario table will always process the entire Transactions table. We need to add an additional Scenario dimension that only contains the distinct scenarios in our model: Actual and Budget. There are now different ways to achieve that. The quickest is to just create a duplicate of the Transactions table, only keep the Scenario column, and run a distinct filter on that:

Figure 1.34 – Power Query table duplication

The other option to add a Scenario dimension table is to create it from scratch in the data model.

1. Rename that table to Scenario and just keep the Scenario column:

Figure 1.35 – Duplicate table

2. We only need the distinct scenarios, so we remove duplicate values:

Figure 1.36 – Removing duplicates

3. The final step is to add the relationship between the new Scenario dimension and the Transactions table in the Model pane:

Figure 1.37 – Adding relationship for the Scenario table

4. Now, we can enhance our report layout by adding the budget scenario; we only need to add the Budget dimension as another field in the Columns canvas of the Visualizations pane:

Figure 1.38 – Column drill down

This will apparently not change much, but if we perform a column drill-down in the Matrix visual, we will see the Actuals and Budget data displayed correctly where data exists:

Figure 1.39 – Expanded columns

Showing column subtotals doesn’t make much sense in this context, so I have hidden those in the Matrix visual’s Properties pane.

These examples reflect some of the typical challenges you will face with financial data models in Power BI. Let’s summarize:

• In nearly all cases, a star schema that separates dimensional and transactional data is the optimal approach. We will see in the coming chapters how much easier this makes calculations.
• You should focus on having as few as possible dimensions with only the columns you really need. If there is a one-to-one relationship between elements in one dimension and another, then these should be part of the same dimension.

In preparation for the book, we have had a bit of interest in how to deal with data volumes. In my experience with Power BI from the start in hundreds of financial projects, this has never been an issue if (and that’s a big IF) the data model is structured properly with clear separation of data/fact and dimension tables. Power BI can deal with data tables with billions of rows, particularly in conjunction with using new storage options in Fabric. I have rarely seen tables for common financial application scenarios that exceed the millions of rows. Power BI Desktop is only limited by the available memory in your machine. Even on 16 GB RAM machines, I have seen 100 million row tables handled reasonably well. The performance is not fantastic, but it works.

Should you really get to the limits, explore DirectQuery (connecting to the data store directly and not importing the data into Power BI) and Fabric options.

In this chapter, we have covered the benefits that finance users can obtain by using Power BI for financial analysis and reporting that have standardized requirements, such as recurring reporting/interactive dashboards and analytical tasks.

We then reviewed the optimal data model structure for financial requirements and covered typical modeling techniques based on a sample dataset included with the book. We then focused on the integration of budget data that resides in a different data source at a different granularity and demonstrated how to best integrate this data with our main fact table. Finally, we covered how to set up a simple financial statement with our data model.

In the next chapter, we will look at implementing typical financial calculations and logic in Power BI that will make our report more insightful using additional visualization options available in Power BI.