In this article, Rishi Sapra, ICAEW member and Microsoft MVP, shares a few of his top tips when working with Power BI, including practical demonstrations of the tips in action.
Here’s Rishi’s top three tips for working with Financial Data in Power BI:
1. Automate your data cleaning and logic steps in Power Query
Getting familiar with Power Query is the most obvious first step in the journey of becoming a data-driven finance professional, as it allows you to automate many of the repetitive steps each period in combining, cleaning and shaping data.
These steps can be done almost entirely within the user interface with no coding required! Applying these steps does create code in the background that you can inspect in the formula bar, in order to make them repeatable (in a way that is deterministic unlike using Gen AI!). In this way, it is similar to recording a macro but the steps are much more transparent and intuitive compared to using VBA or Office scripts.
And best of all, Power Query can be leveraged in Excel as well as Power BI (it's the "Get Data" experience in both), so it's a natural stepping stone into the world of Power BI coming from an Excel background.
In the accompanying video tutorial, we demonstrate how to convert local currency amounts to reporting currency (USD) using exchange rate lookups. This practical example covers several essential Power Query techniques: removing header rows and promoting columns, unpivoting exchange rate data from a wide format (one row per date with currencies in columns) to a long format (multiple rows per date, one per currency), performing single and multi-column merges to lookup currency codes and exchange rates, and creating custom calculated columns using the M formula language. Note that the merge feature in Power Query is similar to VLOOKUP/XLOOKUP in Excel but far more powerful and intuitive to implement (it is actually closer to how joins are done in SQL and affords a far greater level of flexibility in how the results of the merge are returned).
The key advantage of Power Query's multi-column lookup over Excel is that you don't need to create concatenated helper columns—you simply hold Ctrl to select multiple columns in the order you want them matched. Watch the video to see how these automated steps eliminate manual data preparation work each month.
2. Make use of helper tables to define layouts and dynamic calculations
When finance professionals who are used to working in Excel start using Power BI, they often get frustrated with its seeming lack of flexibility. For example, if you want to create an Income Statement in Power BI, just using an Actuals measure and the Chart of Accounts will give you a starting point but probably not exactly what you want—you'll be missing subtotals like Gross Profit, and rows won't be in the desired order.
In Excel you have full control to define the rows of a financial statement and individual calculations for each row/cell within the same column. This is possible in Power BI even with native visuals (as an aside, there are custom visuals that allow you to insert rows/calculations), but using just the standard Matrix visual for an Income Statement requires a different approach and the use of dynamic measures.
The solution is a "helper table" that defines your desired Income Statement layout. This table includes columns for the row header, sort order, calculation type (direct from Chart of Accounts, subtotal, or custom calculation), and format (currency or percentage). Once brought into the Data Model and related to the Chart of Accounts, this helper table drives the Income Statement structure.
The video tutorial demonstrates how to create a chain of measures that dynamically calculate values based on calculation type: direct lookups for Chart of Account headers, running totals for subtotals, and custom formulas for margins. The final step shows how to implement dynamic format strings, allowing the same measure to display as currency (in either reporting or local currency) or as a percentage depending on the row context. Download the example file to see the complete helper table structure and DAX formulas.
3. Add context to tell a story with your finance data
When a user is looking at a report, just showing numbers (eg, Revenue or Gross Profit) without any context isn't particularly helpful as it relies on the knowledge of the user to be able to effectively evaluate the performance of the business based on those numbers. Is a revenue figure of £100k for the year good or bad? Of course that depends, perhaps on what the revenue was last year, or what the budgeted revenue number was. The goal of data visualisation is to reduce cognitive load—to make it as easy as possible for users to evaluate performance and understand what is driving the numbers.
The video demonstrates how to create KPI visuals for key metrics like Revenue, Gross Margin and Net Margin, using prior year values as context. This approach requires a proper date table (marked as such in Power BI) and leverages time intelligence functions like SAMEPERIODLASTYEAR to automatically calculate comparative values. The KPI visual displays the current metric, a trend line, and variance to the prior year target—all in a compact, scannable format perfect for executive dashboards.
The tutorial also covers formatting techniques, including how to rename the default "Goal" label to "Prior Year" and how to display variance as an actual percentage difference rather than a percentage of a percentage. The example file includes a MTD/QTD/YTD selector using Calculation Groups, demonstrating how to make these KPIs even more flexible for users.
Find out more
Connect with Rishi on his LinkedIn, explore his content at www.powerplatformfinance.com, or watch some of his recent ICAEW webinars on Power BI on the Data Analytics Community on-demand content page.
