How to Create an Income and Expense Report in Power BI

Building a clear, accurate income and expense report is foundational to understanding the financial health of your business. While a simple spreadsheet might work initially, Power BI can transform your static numbers into a dynamic, interactive dashboard. This tutorial will walk you through the process of creating a professional income and expense report in Power BI, from setting up your data to building insightful visualizations.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Before You Begin: Getting Your Financial Data Ready

Success in Power BI starts with well-structured data. Before you even open the application, you need to have your income and expense transactions organized properly. For most small businesses and marketers, this data lives in a spreadsheet like Excel or Google Sheets.

How to Structure Your Data

Your source data should be in a simple table format. Avoid merged cells, fancy formatting, or multiple tables on the same sheet. Ideally, your table should have these four essential columns:

• Date: The date the transaction occurred.
• Description: A brief note about the transaction (e.g., "Client Payment - Project X," "Monthly Software Subscription").
• Category: The type of income or expense (e.g., "Client Revenue," "Software," "Advertising," "Office Supplies").
• Amount: The monetary value of the transaction.

Here’s the most important tip: record expenses as negative numbers. This single step makes the math infinitely easier inside Power BI. Your table should look something like this:

With your data structured this way, you're ready to import it.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Connecting to Your Data in Power BI

Once you’ve opened Power BI Desktop, you can connect to your data source.

1. On the Home tab of the ribbon, click Get Data.
2. If your data is in Excel, select Excel workbook. If it's in a Google Sheet, select Web and use the shareable link. (You can get this in Google Sheets by going to File > Share > Publish to web and selecting "Comma-separated values (.csv)").
3. Navigate to your file or paste your URL and click Open/OK.
4. Power BI will show you a preview. Select your table from the navigator and click Load. If your data looks messy, you should first click Transform Data to open the Power Query Editor for cleaning, but for our simple table, Load is fine.

Creating Your Core Calculations with DAX

Now that your data is in Power BI, you need to create the calculations (called "measures") that will power your report. This is done using DAX (Data Analysis Expressions), which is an Excel-like formula language for Power BI.

Don't be intimidated! We only need three simple measures for this report.

In the Report View, right-click on your table name in the Data pane (on the right side) and select New Measure.

1. Total Income Measure

This measure will add up all the positive numbers in your 'Amount' column. Go to the formula bar, delete the default text, and type in the following:

Total Income = CALCULATE( SUM('Financials'[Amount]), 'Financials'[Amount] > 0 )

(Be sure to replace 'Financials' with the actual name of your table and '[Amount]' with your column name).

This formula tells Power BI to sum the Amount column, but only for rows where the Amount is greater than zero.

2. Total Expense Measure

Next, we do the opposite for expenses. Right-click your table again, select New Measure, and enter:

Total Expenses = CALCULATE( SUM('Financials'[Amount]), 'Financials'[Amount] < 0 )

This adds up all the values less than zero, giving you total expenses. Since the numbers are already negative, the result will correctly show as a negative value.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

3. Net Income (Profit/Loss) Measure

This is the easiest measure of all. Because our expenses are already negative, all we need to do is add income and expenses together.

Net Income = [Total Income] + [Total Expenses]

You can also create this measure using SUM('Financials'[Amount]) for a simpler formula, but having separate income and expense measures gives you more flexibility in your report design.

Building Your Report Visuals

With your three core measures created, you can now build a visually appealing and informative report. Here are a few essential visuals to include.

KPI Cards for Totals

The simplest way to display your key metrics is with a "Card" visual. You can find it in the Visualizations pane.

1. Click on an empty space on your report canvas, then click the Card visual icon.
2. With the new card selected, drag your [Total Income] measure into the "Fields" area.
3. Repeat this process twice more to create separate cards for [Total Expenses] and [Net Income].

Arrange these along the top of your report for an at-a-glance summary.

Income vs. Expenses Over Time

A line chart is perfect for showing trends. This helps you easily spot your most profitable months or see when expenses spiked.

1. Select the Stacked column chart visual.
2. Drag your Date field to the X-axis.
3. Drag both the [Total Income] and [Total Expenses] measures to the Y-axis.
4. Power BI will display both. You may want to go into the Format section for this visual and change the color for expenses to red to make it more intuitive.

Breakdown by Category with a Waterfall Chart

A Waterfall chart is the classic way to show how a starting value is affected by a series of positive and negative changes. It's perfect for an income statement.

1. Add a Waterfall chart visual to your canvas.
2. Drag your original Category column to the "Category" field.
3. Drag your original Amount column to the "Y-axis" field.

Power BI will automatically Sum the amounts for each category and show how your revenue is eaten away by various expense categories, ending with your net profit. This is a very powerful way to see where your money is going.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Making it Interactive with Slicers

The real power of Power BI comes from its interactivity. Slicers are filters that anyone viewing the report can use to drill down into the data.

1. Click the Slicer visual in the Visualizations pane.
2. Drag your Date field into the "Field" box. By default, it will create a date range slicer.
3. Add another Slicer and drag your Category field into it. This will create a checklist of all your categories.

Now, you (or your stakeholders) can click on a specific time frame or set of categories, and the entire report will instantly update to reflect that selection.

Final Thoughts

By connecting your structured data to Power BI and creating a few key DAX measures, you can move beyond static spreadsheets and build a fully interactive financial report. This not only gives you a clearer view of your business's performance but also empowers you to explore your data and find answers to your most pressing questions.

While building reports in Power BI is a great skill, it often comes with a steep learning curve involving data prep, modeling, and DAX formulas like the ones we covered. We built Graphed because we believe getting business insights shouldn't be that complicated. We automate the entire process by letting you connect your sources and just ask - in plain English - for a report showing "income vs. expenses by category." In seconds, you get a real-time, interactive dashboard without writing a single formula, letting you focus on the insights, not the setup.