Graphed LogoGraphed
Login

How to Create a Monthly Sales Report in Power BI with AI

Cody Schneider•

Creating a monthly sales report that people actually read and use can feel like a challenge. Instead of exporting static data and arranging it in a spreadsheet, you can build a dynamic, automated report in Power BI that reveals trends and answers questions on the fly. This guide will walk you through exactly how to build it and ways to use Power BI's built-in AI tools to uncover insights that might otherwise go unnoticed.

Why Your Monthly Sales Report Needs an Upgrade

A well-crafted monthly sales report does more than just show last month's numbers. It helps you understand what’s working, what's not, and where you should focus next. A great report allows your team to:

  • Track Performance Against Goals: Instantly see if you're on track to hit quarterly and annual targets.

  • Identify Key Trends: Spot sales momentum for specific products, regions, or sales reps before they become obvious.

  • Make Data-Driven Decisions: Confidently decide where to allocate resources, what sales strategies to double down on, or which underperforming areas need support.

  • Keep Stakeholders Informed: Give everyone from the sales team to the C-suite a clear, consistent view of sales performance without burying them in raw data.

Step 1: Get Your Sales Data into Power BI

Your report is only as good as the data powering it. The first step is to connect Power BI to your sales data source. This could be a simple Excel or CSV file, a cloud application like Salesforce, or a SQL database.

Connecting to a Source

For this tutorial, we’ll use an example with a straightforward method: connecting to an Excel workbook or a CSV file.

  1. Open Power BI Desktop. In the Home ribbon, click Get Data.

  2. Select Excel workbook or Text/CSV depending on your file format.

  3. Navigate to your sales data file and click Open.

  4. A Navigator window will appear, showing you the sheets or tables within your file. Select the table containing your sales data and look at the preview to make sure it's the right one.

At this stage, you have a choice: Load or Transform Data. It's almost always a good idea to click Transform Data first. This opens the Power Query Editor, which is where you clean and prepare your data for analysis.

Cleaning and Transforming Your Data

The Power Query Editor is your data prep workspace. Dirty or inconsistent data leads to inaccurate reports, so taking a few minutes here will save you headaches later. Here are some common data cleaning steps for a sales report:

  • Check Data Types: Make sure columns are formatted correctly. For example, a column with revenue should be a number (like Decimal or Currency), and an order date should be a Date type. Power Query is usually smart about this, but it’s good to double-check. You can change a column's data type by clicking the icon in the column header.

  • Remove Errors or Blank Rows: If your export has blank footer rows or rows with errors, you can easily remove them. Go to the Home ribbon in Power Query and select Remove Rows > Remove Blank Rows or Remove Errors.

  • Handle Null Values: Use the Replace Values feature to replace nulls with a 0 or another useful value, especially in numerical columns.

Once you’re happy with how your data looks, click Close & Apply in the top-left corner. Power BI will now load your clean data into the data model, ready for analysis.

Step 2: Build the Core Visuals of Your Sales Report

With clean data, it’s time to start visualizing. Drag and drop fields from the Data pane onto the report canvas to create visuals. Here are the essential visuals for any great monthly sales report.

1. Key Performance Indicators (KPIs)

KPIs give you an at-a-glance view of your most important metrics. Use the Card visual for this.

  • Total Revenue: Drag the Sales Amount field onto the canvas. By default, it will create a bar chart. Go to the Visualizations pane and select the Card visual (the one that says "123").

  • Deals Won: If you have a column for transaction or deal count, drag it onto a new Card visual. If not, you can drag your unique deal/order ID field and change its summarization to "Count (Distinct)."

  • Average Deal Size: Do the same for Average Deal Size, changing the summarization in the field’s dropdown menu to "Average."

2. Sales Performance Over Time

A line chart is perfect for showing trends. You want to see if sales are growing, shrinking, or staying flat month-over-month.

  1. Select the Line chart visual from the Visualizations pane.

  2. Drag your Order Date field to the X-axis field well.

  3. Drag your Sales Amount field to the Y-axis.

Power BI automatically creates a date hierarchy (Year, Quarter, Month, Day). You can use the drill-down buttons on the visual to move between views, like from annual sales to monthly.

3. Sales by Product, Region, or Sales Reps

A common bar or column chart will show who and what is driving revenue. This helps identify top performers and popular product lines.

  1. Select the Clustered column chart.

  2. Drag a category like Product Category or Sales Rep Name to the X-axis.

  3. Drag Sales Amount to the Y-axis.

  4. Click the three dots (...) at the top of the visual and select Sort axis > Sort descending to show the top performers first. You could also choose to sort by the name of the product or rep.

4. Sales Against Target

If you have a column in your data for sales targets, you can easily visualize performance against goals using a Gauge or a simple table.

  1. Select the Gauge visual.

  2. Drag Sales Amount to the Value field.

  3. Drag your Sales Target to the Target value field.

Now you have a dynamic gauge that immediately shows how close your team is to hitting their monthly or quarterly target.

Step 3: Supercharge Insights with Power BI's AI Features

This is where your report goes from informative to intelligent. Power BI's AI features can help you automatically find insights, explain trends, and answer follow-up questions without building new charts from scratch.

1. Ask Questions with the Q&A Visual

The Q&A visual allows you - or anyone viewing the report - to ask questions in natural language and get an answer in the form of a chart. Think of it as a search bar for your data.

  • Double-click on an empty space on your report canvas. Power BI will automatically suggest adding a Q&A visual.

  • Now, just type in a question. For example: "top 5 products by sales" or "total sales in London last month."

  • Power BI interprets your question and generates a visualization on the spot. If you like the result, you can click an icon to turn that temporary visual into a permanent one on your report.

2. Automatically Explain Trends with Smart Narratives

Smart Narratives creates an automatic text summary of your visuals and report, describing key trends and takeaways in plain English. No more manually typing up what your chart is showing.

  • Click on a chart, like your sales trend line chart.

  • In the Visualizations pane, select the Smart Narrative icon.

  • Power BI generates a text box containing a paragraph analyzing the visual, such as: "Sales saw an increasing trend between January and May, with the steepest increase of 35% occurring in March." The values in the narrative are dynamic and will update as you filter or slice your data.

3. Find Hidden Insights Automatically

Sometimes you don’t even know what questions to ask. Power BI can analyze a dataset on its own to surface interesting patterns for you.

  • In the Data pane on the right, right-click on your dataset name.

  • Select Get quick insights.

  • Power BI takes several seconds to run different analytical algorithms over your data. Then, a notification will pop up. Click View insights to see a page full of auto-generated charts that reveal correlations, outliers, and trends you might have missed.

4. Explore Root Causes with the Decomposition Tree

The Decomposition Tree is an AI-powered visual that helps you perform root cause analysis interactively. It allows you to break down a metric - like Total Sales - by different categories to understand what’s driving it.

  • Add a Decomposition Tree visual to your canvas.

  • In the Analyze field, add your main metric (e.g., Sales Amount).

  • In the Explain by field, add the dimensions you want to explore, like Region, Sales Rep, Product Category, and Month Name. Add a few.

  • To use the visual, click the plus sign (+) next to your main metric. This lets you choose which dimension to break it down by. You can continue clicking the plus signs to drill down layer by layer, exploring why sales were highest in a certain region and which reps were responsible.

Step 4: Automate and Share Your Report

A manual report has a very short shelf-life. Once you’ve built your report, the final steps are to automate its refresh schedule and share it with your team.

  1. Publish to Power BI Service: In Power BI Desktop, click Publish in the Home ribbon to upload your report to your online workspace.

  2. Schedule Refresh: In Power BI Service, navigate to your dataset. Click the three dots (...) and select Settings. Under Data source credentials, enter your credentials. Then, under Scheduled refresh, toggle it on to schedule your data to refresh daily or weekly.

  3. Share Your Report: From Power BI Service, you can share your report in multiple ways: share it with team members via email, embed it into a Teams channel, or create email subscriptions that send out a PDF on a set schedule.

Final Thoughts

Building an interactive, intelligent sales report in Power BI elevates it from being a static document to a valuable decision-making tool. By connecting your data, creating core visuals, and leveraging Power BI's AI features to uncover hidden patterns, you provide your team with the insights they need to drive growth.

While Power BI is an incredibly powerful tool, mastering its various features and DAX formulas can take time. For teams that want an even faster, more accessible way to connect all their sales and marketing data sources - like Salesforce, HubSpot, and Google Ads - and get insights faster, try Graphed as your AI data analyst. You can simply ask questions in plain English like "Create a dashboard showing my Facebook ad revenue over the last 90 days," and have a live, auto-updating dashboard in seconds - no building required.