How to Create an Expense Report in Power BI

Building an expense report from scratch is one of the best ways to get comfortable with Power BI. Instead of just looking at abstract data, you’re working with something tangible: where the money is going. This article will walk you through, step by step, how to connect your expense data and build a clear, interactive, and powerful expense report dashboard in Power BI.

First, Prepare Your Expense Data

Power BI is incredibly powerful, but its output is only as good as the data you give it. A clean and well-structured source file will save you countless headaches. Most expense tracking starts in a simple spreadsheet, which works perfectly for this.

Before importing anything, make sure your data is organized in a proper table format. This means:

• Consistent Columns: Each column represents a single piece of information (e.g., date, expense category, employee name, amount).

• Dedicated Rows: Each row represents a single expense transaction.

• No Merged Cells: Avoid merged cells or complicated formatting. Power BI prefers simple, raw data.

Here’s a simple and effective structure you can use in Excel or Google Sheets:

Example Expense Data Table:

Tip: For better analysis, format your data range as a Table in Excel (select the data and press Ctrl+T). This gives it a defined name and makes it easier for Power BI to work with.

Connecting Your Data to Power BI

With your data prepped, it's time to pull it into Power BI Desktop. The process is straightforward and is the first step for nearly every report you'll ever build.

1. Open Power BI Desktop. From the Home ribbon, click on Get data.

2. If your data is in Excel, choose Excel workbook. If it's in a Google Sheet, you can choose Web and use the sheet's shareable link (make sure an 'export as csv' URL is formatted correctly). We'll assume Excel for this example.

3. Navigate to your saved expense report file and click Open.

4. A Navigator window will appear, showing you the sheets and any tables within the workbook. Select the specific table or sheet containing your expense data. You'll see a preview on the right.

5. Click Load to bring the data directly into your report, or click Transform Data to open the Power Query Editor first. It's always a good practice to choose Transform Data.

A Quick Look at Power Query

Power Query is Power BI's built-in tool for cleaning and reshaping data. Even if your data looks perfect, you should quickly check the data types. Power BI is smart, but sometimes it guesses wrong.

• In the Power Query Editor, look at the icon next to each column header.

• Ensure your Date column has a calendar icon (Date or Date/Time type).

• Make sure your Amount column is a number type (like Decimal Number, with a "1.2" icon).

• Ensure other columns like Category and Employee are text (ABC icon).

If anything is incorrect, just click the icon and select the right data type from the list. Once everything looks good, click Close & Apply in the top-left corner.

Building Your Expense Report Visuals

Now for the fun part: visualizing the data. The goal is to create a dashboard that answers key questions at a glance, such as "What's our total spending?" or "What category costs us the most money?".

You'll build your visuals in Power BI's Report view. The available visuals are in the Visualizations pane, and your data fields are in the Data pane on the right.

1. Key Metrics with Cards

Cards are perfect for displaying single, important numbers. Let’s create cards for Total Expenses and the Number of Transactions.

• Click on the Card visual in the Visualizations pane. A blank card will appear on your canvas.

• With the card selected, drag the Amount field from the Data pane and drop it into the "Fields" area of the visual. Power BI will automatically sum it up, showing your total expenses.

• Add a second Card visual. This time, drag the Description field onto it. By default, it will show the first description. To change this, click the down-arrow next to "First Description" in the Fields area and select Count. Now it shows the total number of approved expenses.

2. Expenses by Category with a Donut Chart

Understanding where money is being spent is crucial. A donut or pie chart gives an instant visual breakdown.

• Select the Donut chart visual from the pane.

• Drag the Category field to the Legend box.

• Drag the Amount field to the Values box.

Instantly, you have a beautiful chart showing the proportion of spending across categories like Software, Travel, and Meals.

3. Spending Over Time with a Line Chart

Is spending increasing or decreasing? A line chart reveals trends over time.

• Select the Line chart visual.

• Drag the Date field to the X-axis. Power BI creates a date hierarchy (Year, Quarter, Month, Day) for you. You can use the drill-down buttons on the visual to change the level of detail.

• Drag the Amount field to the Y-axis.

You can now see the ebb and flow of expenses - for instance, spotting a spike in travel costs during conference season.

4. Top Spenders with a Bar Chart

If you're tracking expenses by employee or department, a bar chart makes it easy to compare.

• Click on the Clustered bar chart visual.

• Drag the Employee field to the Y-axis.

• Drag the Amount field to the X-axis.

The chart will automatically sort from highest to lowest spender, clearly highlighting top contributors to expenses.

5. Detailed View with a Table

Dashboards are great for high-level views, but sometimes you need to see the underlying details. A table is perfect for this.

• Click the Table visual.

• Drag in the fields you want to display, such as Date, Employee, Category, Description, and Amount.

• You can click the column headers on the table visual itself to sort the data.

Making Your Report Interactive with Slicers

This is what separates a fantastic Power BI report from a static image. Slicers are interactive filters that allow users to dig into the data themselves.

Let's add slicers for Date Range and Category.

• Click on a blank part of your canvas, then select the Slicer visual.

• Drag the Date field into the Field box. The slicer will default to a 'between' slider, which is perfect for selecting date ranges.

• Add another Slicer visual. This time, drag the Category field in. This will create a list of checkboxes.

Now, when you select a category (like "Travel") or adjust the date slider, all the other visuals on your report will instantly update to show data only for your selection. This interactivity empowers anyone viewing the report to answer their own follow-up questions.

Using Simple DAX Formulas (Measures)

DAX (Data Analysis Expressions) is the formula language of Power BI. While it can get incredibly complex, you don't need to be an expert to use it. Creating simple "measures" can make your report more robust and easier to manage.

To create a measure, right-click on your data table in the Data pane and select New measure.

Total Expenses Measure

Instead of relying on implicit sums, you can create an explicit measure. This is a best practice that pays off in more complex reports.

Total Expenses = SUM('YourTableName'[Amount])

Replace 'YourTableName' with the actual name of your table. Now you can use this "Total Expenses" measure in your cards, charts, and tables instead of the raw Amount field.

Average Expense Amount

Another simple but useful measure can be created with the AVERAGE function.

Average Expense = AVERAGE('YourTableName'[Amount])

Drop this into a Card visual to instantly see the average cost per transaction.

Final Thoughts

Congratulations, you now know the complete workflow for building a dynamic, insightful expense report in Power BI. You connected to data, built several key visualizations, and added interactive slicers that allow anyone to explore spending patterns and discover trends on their own. This is a foundational skill that opens the door to creating even more complex and valuable operational dashboards.

While mastering tools like Power BI is an invaluable skill, we know it often involves a steep learning curve and hours of setup. If you just need clear answers from your business data without the technical overhead, we're building Graphed to help. You can connect your data sources in a few clicks, then create entire dashboards just by asking questions in plain English - like "Show me total expenses by category last month." We turn hours of report-building drudgery into 30-second conversations, so you can focus on insights, not setup.