By Ziga Milek • Last updated

The Definitive Guide to Income Statements in Power BI

Share this

This is the definitive guide to income statements in Power BI. It includes tips and tricks, new features in Zebra BI visuals, and specific best practices for making your income statements more understandable, actionable, easier to create, more flexible to work with, and much easier to maintain. We will look at how to prepare your data, how to make a basic income statement in Power BI, and how to move forward once you know the basics.

Note: This Definitive Guide to Income Statements in Power BI is based on our 1-hour webinar on the same topic. If you prefer to watch the video, scroll to the bottom of this tutorial, enter your details, and we'll send you the webinar recording and all PBIX examples to go along with it.

The first look at our Power BI income statement

Here is what we will be building - our income statement, Profit and Loss statement, or P&L statement, which is much more than that.

Finished income statement in Power BI
Finished income statement in Power BI

Let's look at the main features:

  • This is a hierarchical statement. You can expand and collapse specific rows - for example, the cost of goods sold in this example comprises product cost, service, and other costs. We are using a hierarchy with several levels.
  • They included calculations. You can track KPIs, such as operating income as a percent of revenue or net income as a percent of revenue.
  • Live comments. Different comments are shown to explain your data if you switch between months.
  • Report view switching. In addition to switching between month-to-date and year-to-date views, you can switch from the income statement to the balance sheet and cash flow views. Each of these views also contains dynamic comments and all other features. One simple slicer gets you access to the holy trinity of financial statements.

The anatomy of a financial report

So what do you need to build something like this?

The anatomy of a financial report (income statement in Power BI)
10 things making up an effective financial report, such as an income statement

Here are the 10 things making up this effective report:

  1. The calendar filter because you're reporting by month and year.
  2. Period calculations so you can switch between month-to-date and year-to-date views and full-year views.
  3. Account hierarchy is crucial for every financial report. The structure of your accounts needs to be clean and merge data from several sources. It has to have a proper hierarchy across several levels and so on.
  4. Subtotals are essentially calculations of your rows. For example, the cost of goods sold comprises product, service, and other costs.
  5. Results, which are similar to subtotals but offer more. Instead of just aggregating your financial data, they are calculations. Take the gross margin row, for example, as it is calculated as revenue minus the cost of goods sold.
  6. Inverted rows that allow us to work effectively with KPIs. In financial reporting, some KPIs are wrong, and some are good, so you need a way to handle this in your visualization.
  7. Report switch to move between different reports.
  8. Visualization so you don't have to rely on tables alone but can tell a story with a combination of tables and charts. In this example, we used very effective waterfall charts.
  9. Variances are at the heart of any business report, like the income statement. They show how your results stack against your plan, the previous year's results, etc.
  10. Comments are very important for financial reporting as financial reports are typically delivered to the top management, who prefer comments. They need short explanations that explain what's going on.

Preparing the financial data model

Where to start if you want to build a Power BI income statement? First, you must fix your data, which is a significant challenge.

Let's start with the ideal financial model. Ideally, your fact table would consist of nothing but numbers.

The main fact table with numbers in Power BI
Your main fact table with numbers shown in Power BI

In addition to values, our fact table has data about accounts (AccountID), business units (BusinessUnitID), date, and scenarios, which describe whether this data is actual, plan, or forecast data.

Around this data, you have dimensions. You probably have a table named Accounts. This table should contain the whole hierarchy of your accounts and the entire structure. This table is then related to your fact table to ensure that accounts properly filter your values and other data.

A sample accounts table in Power BI
A sample accounts table

Next are the Calendar table, your fact table, and the Period calculations table. For financial reporting, it is recommended that you use a short and straightforward disconnected table for your period calculations.

A calendar table for income statements in Power BI
A sample calendar table

You might also have business units or regions or another way to group and categorize your data.

If you want to use dynamic comments, create another fact table containing all your comments. It should be linked to your calendar to ensure your comments refer to a specific month.

You also might want to link your comments table to your account table to comment on specific accounts, such as product revenue, costs, or a specific cost type. If you need comments on specific regions or business units, relate your comments table to these dimensions.

Below is the screenshot of a sample data model showing the central fact table (Financials) with comments (another fact table) and individual dimensions, such as business units, accounts, calendar, and period calculations. The data model also shows relations between individual fact tables and dimensions.

The financial data model for Power BI income statements
The basic data model for financial reporting

Merging the data

The next step is to populate the data into our model.

You should put all your data - actuals, plans, and previous year - into a single table.

The important thing is to prepare a hierarchy of your accounts, such as product revenue, service revenue, gross margin, etc. You might have several levels of hierarchy. For example, your inventory falls under the current assets group that falls under the more general assets group. Each of these columns needs to have an ID. These IDs will serve you as you display your data in different reports.

We used a trick to create a slicer for switching between different types of reports. We have merged the accounts from the income statement with those from the balance sheet and cash flow statements.

A table with merged accounts from different report types
A table with merged accounts from different report types

We now have all the accounts in the same table. The idea here is very simple - after merging the tables, we added another column (ReportType) that marks each row with the report type. This allows you to filter out all the accounts for the income statement, balance sheet, or cash flow report.

We'll use this ReportType column to create a slicer a bit later.

Marking the important KPIs

Here is another helpful trick. We have marked certain accounts. Such as Revenue or Gross margin as an important KPI. This value is stored in the IsKPI column and marked with value 1.

Creating an IsKPI column that will help us quickly filter out the most important KPIs
Creating an IsKPI column that will help us quickly filter out the most important KPIs

This will help us build quick financial reports from across the three types of financial reports. We can mark certain accounts from our Power BI income statement, balance sheet, and cash flow statement as necessary. Then, we can quickly create reports with data from all three statements.

We can do that because we merged everything into a large data table.

Creating a basic income statement in Power BI

Before we begin designing, a quick note: we will not cover the switch between the year-to-date and monthly views or commenting. We have covered this in our past webinars - Top 5 DAX tricks for super effective Power BI dashboards and Dynamic Commentary in Power BI.

Let's add a Zebra BI Table. We're using the latest version that offers some exciting features.

Start by adding the actuals (Value AC) and the comparison to the previous year (Value PY) from the Financials table. Then add the Account field to the Category placeholder to get a simple income statement.

A simple income statement in Power BI
A simple income statement in Power BI

The next step is to add the Account Groups field and drop it in the Category placeholder in the Visualizations pane.

Switch the previous year (PY) and actual revenues (AC) columns to the waterfall chart view.

Switching the chart to the waterfall chart view
Switching the chart to the waterfall chart view

Then switch sorting to Sort by Account Group and Sort ascending.

Setting up sorting in the chart
Setting up sorting in the chart

This is something you need to get right in your data. To get the sort order of your accounts right, you have to make sure that the IDs of your accounts are sorted in the right order in your table of accounts.

You need to give the correct IDs to all your accounts - for example, your product revenue will be your number one, your service and other revenue will be number two, and so on.

The same goes for your account group IDs. Your revenue here is the first account group. Your cost of goods sold is the second account group, and so on.

If you didn't do this, your accounts would have been sorted alphabetically by default, and you don't want that.

If you'd like to learn more about sorting, you can read the article in the Knowledge Base about options for Sorting rows in the Zebra BI Tables visual or how to sort a hierarchy by custom sort logic.

Making sure the data is displayed correctly

Once we have that in place, we must ensure that the rows with expenses, costs, and so on are inverted. You have two options to do this.

If you're using regular native Power BI visuals, there's no way to handle this. You must return to your data and ensure your costs are negative numbers. Many people do that, but we believe keeping positive numbers across the board is better.

If using the Zebra BI visuals, right-click the row name and ensure you invert this number. You can invert numbers like product and service costs.

Inverting the costs of products and other rows
Inverting the costs of products

These costs make up the cost of goods sold subtotal, which is automatically summed up and inverted because we have correctly set up the hierarchy of accounts.

A gross margin account is another type of subtotal. However, it's a result. In Zebra BI, right-click on it and mark it as a result. Zebra BI then takes the revenue and the costs to calculate the gross margin immediately.

Marking the Gross margin account as a result from revenue and costs
Marking the Gross margin account as a result of revenue and costs

You should then invert your operating expenses as they constitute costs and mark Operating Income as a Result. Income before income taxes is another result, and the Provision for income taxes is also inverted as an expense.

Your final income is, of course, a result of preceding accounts.

If you use Zebra BI, you don't have to do anything special about variances because our extension does it all for you. It automatically adds the absolute variance to the previous year and the relative variance to the previous year. Find out more about presenting variances in a way that makes an impact.

Absolute and relative variances in Zebra BI
Absolute and relative variances in Zebra BI

Adding a formula to your Power BI income statement

Next, we are adding a formula for the gross margin percentage. The formula editor is a new feature only available in Zebra BI 4.5.

We'll add the Gross margin percentage by using a formula. Right-click on the account name and click Add Formula.

Adding a formula to your report
Adding a formula to your report

The formula for this is:

Gross margin % = [Gross margin] / [Revenue]

It's all very intuitive. Start typing Gross margin, and Zebra BI will suggest the right account here. Confirm the suggestion by clicking or pressing Tab.

If you are working with a percentage number, click the percent button to format this formula as a percent.

You also have some formatting options, such as decimal places, bold, and italics. Once you're done, click Add. This adds an entire row and calculates values for all columns.

Interface for adding and editing formulas
Interface for adding and editing formulas

You can do the same for other calculations, such as operating income as a percentage of revenue. Add another formula:

Operating income % = [Operating income] / [Revenue]

Switching between three financial statements

Now let's look at the slicer. The easiest way to understand how it works is to remove it. Once you remove it, all your accounts are shown - your P&L, cash flow, and balance sheet.

Power BI income statement with the slicer removed
Power BI income statement with the slicer removed

The easiest way to restore it is to take the Report Type field from your Accounts table and drop it onto the visual. Once you have it there, turn that part of the visual into a slicer using your Visualizations pane. You can use it as a dropdown menu or turn it into a horizontal list for easier switching.

You can then move on to editing your balance sheet. You must also invert liabilities to make sure your visual makes sense.

Adding dynamic comments

While we previously explained how to build a comments table, I would like to share how to add comment markers in your visual to indicate where comments belong.

To learn more about creating dynamic comments in Power BI, look at our ultimate guide (+ PBIX example) here: Dynamic Commentary in Power BI.

This is another new feature of Zebra BI 4.5, and it adds a new placeholder to your Visualizations pane. Add two fields to it: comment number and the actual Comments. Add the Comments field as a secondary field in the Comments placeholder.

The new Comments placeholder in Zebra BI
The new Comments placeholder in Zebra BI

Zebra BI visual lets the user hover over the numbers on the visual and see the exact tooltip text above the comment marker.

You can even design the tooltips. Do this in the standard tooltip settings of Power BI, where you can choose the color, text size, transparency, and other properties.

Advanced income statements in Power BI

You have now laid the groundwork for different reports. For example, you might not want to switch between months using the dropdown menu and would like to have them in one table.

To do that, take your Month field from your Calendar group and put it in the Group placeholder. Then remove the dropdown month selector. This creates a wide table that needs to be worked on a bit. You can condense it and focus on actual and variances, hide some columns, and reduce the font size.

There is one more thing you can do on a table like this. You can add quarters where the months making up individual quarters can be collapsed or expanded.

Quarters grouping results in the income statement
Quarters grouping results in the income statement

It's easy to do. Just add your Quarters field on the top of the Month field in your Group placeholder.

Quarters added to the Group placeholder in the Visualizations pane
Quarters added to the Group placeholder in the Visualizations pane

You could also add a grand total which is essentially your year-to-date value. Hover over the Q1 label on your chart so a small plus sign is displayed. Click on it and select the Show grand total item. This will add the grand total as the last column in the table.

Adding Grand total
Adding Grand total

Using Period calculation

You can build on this view and create a month versus year-to-date view. Remove the Month and Quarter fields from your Group. Then, instead of using the MTD and YTD slicer, you can take your period calculation and put it in the group placeholder.

Power BI income statement: month-to-date and year-to-date visualizations on the same page
Visualization showing month-to-date and year-to-date tables on the same page

This gives you a tremendous single-page overview of your month-to-date and year-to-date data. You can select individual months on the horizontal month slicer to switch between them.

Instead of the period calculation, you can now bring in your business units to do a breakdown by business unit. Replace the Period calculation field in your Group placeholder with the Business unit field to get the income statement for all the business units.

A Power BI Profit&Loss statement by business groups
P&L statement by business groups

The powerful thing here is that you have all the data ready, and you could easily switch to a cash flow view if you had the data. And that is the beauty of merging all your accounts into one big accounts table. You can do all your financial reporting directly using just one data model.

Trend charts

One thing that is kind of chronically missing in financial reporting is trend charts. Financial reports are typically just tables, although it doesn't have to be like this. You should ensure that you also include charts showing the trends in the movement of values. Let's look at the example we'll be building.

Trend charts (small multiples) showing movements in values from the income statment
Trend charts showing movements in values

Start by adding the Zebra BI charts visual. We'll compare actual values versus the previous year's values broken down by month. Add Value AC to your Values placeholder, Value PY field to your Previous Year placeholder, and Month field (Calendar table) to your Category placeholder.

Creating trend charts
Creating trend charts

To make sure you can switch between different KPIs, pull your Account groups field onto your workspace. This lists KPIs you can switch between gross margin, operating expenses, operating income, net income, etc.

An even better approach is to take the Account groups field and pull it onto your Group placeholder. This creates multiple charts within the same visual or small multiples. This is one of the best features of Zebra BI charts.

Visual displaying small multiples with the most important P&L KPIs such as revenue, gross margin, net income, etc.
Small multiples with the most important KPIs

Because we merged all the accounts, you could also switch from this P&L statement to your cash flow report balance sheet.

Let's look at one more trick here. If you look at the chart, you see your revenue grew by 4.5%, but your gross margin stayed almost the same. Something happened here, and you can find the culprit in the cost of goods sold, which grew by 12.5%. You need to invert this element to show this is a negative development. Simply right-click on the label and select Invert.

Inverting a chart to show negative trends
Inverting a chart to show negative trends

The waterfall (bridge) chart

The bridge chart is interesting, especially for the cash flow or net income. Essentially, you have the opening and closing balance of your cash. You take all the changes and present them in a waterfall chart.

A waterfall chart showing the impact of different factors on cash flow
A waterfall chart showing the impact of different factors on cash flow

In this case, we could invert the chart, something made possible by the Zebra BI charts visual. In the Visualizations pane, click the Format button and expand the Chart settings. Then toggle the Show vertical charts option.

Inverted bridge chart showing a breakdown of the cash balance
Inverted bridge chart showing a breakdown of the cash balance

The bridge charts work quite nicely in financial reporting, and the financial management and the top executives love these types of charts. You can do the same thing with the net income plan or the previous year and compare it to net income actuals. If you are unsure which chart to use, our tutorial on choosing the right visual will help.

How to impress with your Power BI income statements

This guide was all about the tips and tricks to create income statements in Power BI that tell a story and provide intuitive and actionable information for business decision-makers. Look at some other best practices to create attractive and insightful financial reports.

We began by preparing the data and data model for a rich visual report that integrates three of the most common financial reports - income statement, balance sheet, and cash flow report.

Leveraging advanced Zebra BI features, we turned a basic income statement report into a rich and meaningful report that integrates groups, variances, dynamic comments, calculations, and period calculations.

Enter your data below and get the follow-along PBIX file to try this.

You can also download a 30-day free trial of Zebra BI visuals for Power BI and start working on your own data and stories.

8 comments

  1. This is the definitive guide to income statements in Power BI. It includes tips and tricks and a couple of new features in Zebra BI visuals. I have been able to construct full financial statements that conform to predefined layouts.

    1. Hi Asif, only the result is bolded by default, all the other things you need to bold manually. Right-click on the Row >> Format >> B. Hope this helps. 🙂 If you need more details check out this article.

Leave a Comment

Want to join the discussion? Feel free to contribute!

March 28, 2024

Live Zebra BI Power BI Demo

Register Now